How to flag non-filtered rows from slicers?

[espinozr]

Hello,

I am using a large table (19K rows / 19 columns), and then I summarize some results with a series of “SUMIFS” and multiple criteria. This works fine, but now I would like to add some slicers for some of the conditions, and use a combination of both sumifs (based on cell values), and filters using the slicers.

My problem is that the SUMIFS sums the filtered rows from the slicers. Is there a way to add a formula where I can flag the non-filtered rows so I can use that as a condition in the SUMIFS? (example, non-filtered rows = 1, filtered rows = 0).

I was reading about SUBTOTAL, but I think this doesn't work for me, because on top of the slicers I'm also using some other conditions based on some cell values. I need to be able to use the multicriteria conditions of the SUMIFS.

Thanks,

 

[JamesCanale]

If I understand you, this is what I do under those circumstances:

MrExcelPlayground18.xlsx
	G	H	I
3	Thing	Hidden	Value
4	A	1	2
5	B	1	3
6	C	1	4
7	D	1	3
8	E	1	4
9	F	1	5
Sheet22

Cell Formulas
Range	Formula
H4:H9	H4	=AGGREGATE(3,5,[@Thing])

 

[espinozr]

Hi, thanks for your reply.

The problem is that I want to combine both filtering using a slicer PLUS some conditions in a SUMIFS, and the SUMIFS does include the hidden rows. I'd need to have an additional column where I could flag whether the row is hidden or not, so I can include that as a condition in the SUMIFS. Otherwise, it sums even the filtered rows from the slicers.

If I understand well from your answer, this would work if every condition is filtered using the slicer, but not in a SUMIFS. Am I correct?

Why am I complicating myself using slicers and SUMIFS? because I have several criteria (+15), and some of them are not practical in a slicer. They are directly linked to other spreadsheet, so I would like to use them as criteria for the SUMIFS, an then add just a few categories in the slicers.

Something like this:

The only way I can think this could work is by finding a way to flag the filtered rows (example, if row hidden = 0, not hidden = 1). Any ideas?

 

[JamesCanale]

In my example, Column H is that flag. Use sumifs, and include column H=1. Column H will contain '1' when the row is visibile, and though you'll never see it, a '0' when the row is invisible (filtered out by the slicer).

That is what AGGREGATE(3,5,...) is. The 3 is count (for one thing, it will only ever be 1). The 5 is 'ignore hidden', so it will return zero.

 

[espinozr]

In my example, Column H is that flag. Use sumifs, and include column H=1. Column H will contain '1' when the row is visibile, and though you'll never see it, a '0' when the row is invisible (filtered out by the slicer).

That is what AGGREGATE(3,5,...) is. The 3 is count (for one thing, it will only ever be 1). The 5 is 'ignore hidden', so it will return zero.

Oh, I get it now. This is exactly what I needed, thanks for your help!