Subtotal with condition

Hi All,

I am having an XL file, where I would like to get the subtotal of a particular field based on a condition.

Please find the below sample file.

Where I need the Subtotal (Count) of Records where Name = "A". Please note that the for the region filed, i am having a slicer.

Name Number Region
A 65 South
B 73 North
C 7 West
A 49 South
C 59 South
V 93 North
A 31 West
C 76 East
D 77 East
S 42 North
W 40 North

Regards,
Manoj

Try this:

=SUMIF(A2:A11,"A",B2:B11)

Hope this helps.

Pete

If you are looking for subtotal after filter then try this :

Formula:

=SUBTOTAL(3,A$2:A$11)

Or

Formula:

=SUBTOTAL(103,A$2:A$11)

And if you want count in a regular way then :

Formula:

COUNTIF(A$2:A$11,"A")

Hi All,

Thanks for your support.

But I think, the above solutions will now work for my issue.

Please find the attachment for your reference.
Capture.PNG

I am having a slicer for filtering Region. And after the filter, i need to see the count in that region against each name.

Thanks!

Regards,
Manoj

Thanks a lot.

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$12)-ROW($B$2),0)),($A$2:$A$12=$A13)+0) served my purpose.

If you don't mind, can you please explain the same.


Regards,
Manoj

Thank you.....