Using Subtotal with countifs

[rishilaughs]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Content[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]Random Content 1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Random Content 2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Random Content 3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Random Content 4[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Random Content 6[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

Dear,

I know this type of question has been addressed before but I was having problem understanding the multiple formulae used and how it could be applied to my specific problem.

What i am trying to do is, use Subtotal function in conjunction with Countif

For the given problem, please guide how to count(CountA) category (either A, B,C) in a filtered row using formulae.

I am using http://www.mrexcel.com/forum/excel-questions/698211-combine-countif-subtotal.html this as reference but having problem understanding so many nested formulae.

Thanks

 

[Aladin Akyurek]

Thanks Aladin for the help!
It showed me formula parse error.
I will try to understand what all these nested function convey and where could be possible error
But for the moment Jonmo1 reply saved me for the moment!

Best Regards,

The equality test...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C10,ROW(C10:C1000)-ROW(C10),0,1)),(I10:I1000="A")+0)

 

[yanivp1]

I want to combine to formulas:


The first:
=COUNTIF(G8:G110,"2")+COUNTIF(G8:G110,"1")


The second:
=SUBTOTAL(9,G8:G110)


Is it possible?


Thanks for any help!

 

[Aladin Akyurek]

I want to combine to formulas:


The first:
=COUNTIF(G8:G110,"2")+COUNTIF(G8:G110,"1")


The second:
=SUBTOTAL(9,G8:G110)


Is it possible?


Thanks for any help!

If it must be a sum...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G8,ROW(G8:G110)-ROW(G8),0,1),--ISNUMBER(MATCH(G8:G10,{1,2},0)))

If it must be a count...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(G8,ROW(G8:G110)-ROW(G8),0,1),--ISNUMBER(MATCH(G8:G10,{1,2},0)))

 

[yanivp1]

I can't paste the formula. When i paste it I recieve an error.

 

[Aladin Akyurek]

edited:

If it must be a sum...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G8,ROW(G8:G110)-ROW(G8),0,1)),--ISNUMBER(MATCH(G8:G10,{1,2},0)))

If it must be a count...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(G8,ROW(G8:G110)-ROW(G8),0,1)),--ISNUMBER(MATCH(G8:G10,{1,2},0)))[/QUOTE]

 

[yanivp1]

Still the count formula has an error.

 

[yanivp1]

It works! Thanks a lot!!!
You just type G10 and not G110.

 

[Aladin Akyurek]

It works! Thanks a lot!!!
You just type G10 and not G110.

You are welcome.

 

[yanivp1]

May you please helpme with another simmiliar formula that combine this two formulas:

The first:
=COUNTIF(N8:N119,"1")

The second:
=SUBTOTAL(9,N8:N119)

Thanks a lot in advance,
Yaniv.

 

[Aladin Akyurek]

May you please helpme with another simmiliar formula that combine this two formulas:

The first:
=COUNTIF(N8:N119,"1")

The second:
=SUBTOTAL(9,N8:N119)

Thanks a lot in advance,
Yaniv.

Looks like:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(N8,ROW(N8:N119)-ROW(N8),0,1)),--(N8:N119=1))

​