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?

 

[TheCman81]

You can use sumif within subtotal, you just have to approach it differently.

Something like this would sum a filter range based on the criteria being being "limited"

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2:$B$10,ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10)),,1)),--($A$2:$A$10="Limited"))

You can pretty much do anything with Excel

 

[edecaso]

Col.Row
A	B	C	D	E
1	Record	Type	Amount	Filter
2	1	Tax	1200	A
3	1	Fin	5200	B
4	2	Tax	4500	C
5	2	Fin	3500	B
6	3	Tax	4600	B
7	3	Fin	5700	B
8
9	SUMIF TOTAL	Tax	4600		Regardless of Filtering
10		Fin	14400
11
12	SUBTOTAL SUMIF	Tax	?		To consider only filtered items
13		Fin	?

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

What would my formula in D12 and D13 be:

I am having a hard time interpretting your below answer from a previous reply of yours to another user and and how to apply to my data (sample above)
'=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2:$B$10,ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10)),,1)),--($A$2:$A$10="Limited"))

 

[Jonmo1]

You can also use a Helper column with the count or counta function for each cell in a column.
Then use that column if a countifs

if you want to do say
=COUNTIF(A$2:A$100,B2)
But only count the visible rows

Put this in an available column (Say C)
In C2 and filled down put
=SUBTOTAL(3,A2)

Then you can use
=COUNTIFS(A$2:A$100,B2,C$2:C$100,1)

 

[Aladin Akyurek]

...

D12, copied down:

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

 

[edecaso]

Ooops, now I'm even more confused.

Basically in my text version data sample in D12, I want to have a formula that will add column D amounts if Column C values are equal to C12 criteria but only adding up the filtered displayed items.

 

[Aladin Akyurek]

Ooops, now I'm even more confused.

Basically in my text version data sample in D12, I want to have a formula that will add column D amounts if Column C values are equal to C12 criteria but only adding up the filtered displayed items.

See post #5...

 

[edecaso]

Is $2$2 in your sumproduct(subtotal) answer supposed to be $D$2. Even if I change to $D$2, Excel tells me "too few arguments for this function"

 

[Aladin Akyurek]

Is $2$2 in your sumproduct(subtotal) answer supposed to be $D$2. Even if I change to $D$2, Excel tells me "too few arguments for this function"

I already edited the bit...

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

 

[edecaso]

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))