Subtotal Function and Countif/Sumif

[SpikeGiles]

The subtotal function is great when working with data you want to filter several ways, but it lacks the functionality that would exist if it had a countif and a sumif code, unless I am missing something. Does this functionality exist within Excel? If not, is there a work-around?

 

[Aladin Akyurek]

Your edited code is still not working for me. It results in 0, whether I filter or not.

This is what I have in my formula cell

=SUMPRODUCT(SUBTOTAL(9,OFFSET($D$2:$D$10,ROW($D$2:$D$10)-ROW($D$2),0)),--($C$2:C$10=$C12))

My fault. While trying to change from the MIN form to the MIN-less form, I've made mistakes...

Record	Type	Amount	Filter
1	Tax	1200	A
1	Fin	5200	B
2	Tax	4500	C
2	Fin	3500	B
3	Tax	4600	B
3	Fin	5700	B
SUMIF TOTAL	Tax	10300
	Fin	14400
SUBTOTAL SUMIF	Tax	10300
	Fin	14400

<tbody>
</tbody>

D12, copied down:

=SUMPRODUCT(
  SUBTOTAL(9,OFFSET($D$2,ROW($D$2:$D$7)-ROW($D$2),0)),
  --($C$2:$C$7=$C12))

Now try to filter on some item in E.

 

[edecaso]

Now it does sum okay, but when I filter the list to hide an item, it still considers it in the sum.

I also found the following in another post and same thing happens with this formula, it incorrectly includes the hidden row in the sum

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows
=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))

<tbody>
</tbody><colgroup><col><col span="12"><col></colgroup>

 

[Aladin Akyurek]

Now it does sum okay, but when I filter the list to hide an item, it still considers it in the sum.

I also found the following in another post and same thing happens with this formula, it incorrectly includes the hidden row in the sum

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows
=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))

<tbody>
</tbody>

The formula (the last one I posted) is now correct.

The one above includes incorrectly the header row, It should be:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(A2:A100="x"))

would be correct.

If you hide manually, use 109 instead of 9 in SUBTOTAL.

 

[edecaso]

Aladin, if you are saying that the last one in your reply #11 is the correct one, this is the one I had used for my reply #13 when I made my comment that it includes filtered hidden items in the total and it shouldn't. The other exampl (also in #13), I had adjusted for my data so I wasn't including the header rows, also includes filtered hidden items in the total. Is there a system setting or option that these formulas are including it in the sum even when hidden?

 

[Jonmo1]

Can you post a working formula that returns the desired result before rows are filtered/hidden?

And are rows hidden via the AutoFilter, or by right click - Hide ?

 

[Aladin Akyurek]

Aladin, if you are saying that the last one in your reply #11 is the correct one, this is the one I had used for my reply #13 when I made my comment that it includes filtered hidden items in the total and it shouldn't. The other exampl (also in #13), I had adjusted for my data so I wasn't including the header rows, also includes filtered hidden items in the total. Is there a system setting or option that these formulas are including it in the sum even when hidden?

Post #11 includes a formula that effects a condtional sum. If you also apply autofilter, the formula will only pick up the visible cells.

 

[_Wrok]

Sorry to resurrect such an old thread but I am having trouble adapting this information to my spreadsheet. I import data from an access DB and would like to do this same task but with two modifications.
I would like to be able to use the columns name for the range and count the number of 'FALSE' entries.
=SUBTOTAL(COUNTIF([Incident], FALSE)) is where I started and I am just now realizing how this wont work :P
I am using the 2013 suite. Do I have any chance of making this work.

-Thanks

 

[Aladin Akyurek]

@_Wrok

Does this work for you?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(A2:A100=FALSE))

where A2:A100 = Incident and B2:B100 a correlated range.

 

[_Wrok]

Does this work for you?

Thank you for the reply. Unfortunately it does not work since I do not have a static range. The range can change since the information is being imported from access.

 

[Aladin Akyurek]

Thank you for the reply. Unfortunately it does not work since I do not have a static range. The range can change since the information is being imported from access.

But does it work for the data that you just now have?