Hi, I have a question, I have a list of agents (first column) with team leaders (second column) and with let's say letters: a, b, c, d (in a third column). I need to count how many "b" letters are there, but with filter turned on second column: e.g. Team_leader_1. thanks for your help, regards, Michal (momi@email.cz)
special subtotal
- Thread starter momi
- Start date
Ah, sorry, missed the part about a filter. For that, you need a helper column with this formula:
=SUBTOTAL(103,A2)
(I'm assuming there's actually something in col A)
Assuming the helper column is col d, the formula would be:
=SUMPRODUCT(--(C2:C100="b"),D2:D100)
How it works?
The subtotal function will display 1 if the row is visible, and has a value of 0 if row is not visible. plugging this into the SUMPRODUCT allows us to only look at visible rows that have a "b" in col C.
=SUBTOTAL(103,A2)
(I'm assuming there's actually something in col A)
Assuming the helper column is col d, the formula would be:
=SUMPRODUCT(--(C2:C100="b"),D2:D100)
How it works?
The subtotal function will display 1 if the row is visible, and has a value of 0 if row is not visible. plugging this into the SUMPRODUCT allows us to only look at visible rows that have a "b" in col C.