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]

What do you want to conditionally count while autofilter is applied to the data area of interest?

 

[rishilaughs]

Thanks Aladin

I want to count categories (How many are there) in a sheet of 2000 columns?

What do you want to conditionally count while autofilter is applied to the data area of interest?

 

[Jonmo1]

Can you post the formula you would use before you decided you wanted to ignore rows hidden via filtering ?

 

[rishilaughs]

That's what i am having problem understanding to my case.
But i believe it would be more or less like
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C10:C1000,ROW(C10:C1000)-MIN(ROW(C10:C1000)),,1))*(C10:C1000>50)) ( copied from post )

If everything would have been that easy i would have used formula like
=Subtotal(3,COUNTIF(I1:I1000,"A" )

I assumed that category Column is I and i am counting only 'A' category

Thanks

 

[Aladin Akyurek]

Thanks Aladin

I want to count categories (How many are there) in a sheet of 2000 columns?

Try something like:

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

 

[Jonmo1]

You're not understanding what we're asking for.

Imagine for a moment that COUNTIFS did in fact ignore hidden rows already.
What would your formula look like ?

But given your last post, I think I get it.

Assuming you're using XL2007+
I'd suggest a helper column, say column J for example
J1 and filled down put
=SUBTOTAL(3,I1)

Then use
=COUNTIFS(I1:I1000,"A",J1:J1000,1)

 

[rishilaughs]

Thanks Jonmo1

It was a great help!

Best Regards,

You're not understanding what we're asking for.

Imagine for a moment that COUNTIFS did in fact ignore hidden rows already.
What would your formula look like ?

But given your last post, I think I get it.

Assuming you're using XL2007+
I'd suggest a helper column, say column J for example
J1 and filled down put
=SUBTOTAL(3,I1)

Then use
=COUNTIFS(I1:I1000,"A",J1:J1000,1)

 

[rishilaughs]

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,

Try something like:

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

 

[Jonmo1]

You're welcome.