Subtotal to count 2 criteria.

Hi All,

So I want to count if how often both "M" appears in A2:A20 & "Y" appears in B2:B20, i only want this the count the visible data (the data i have filtered to)

I've attached an example spreadsheet of what i want the formula to do. (Don't think i explained clearly enough)

Sorry that was a bad example :L column A doesn't always contain "M"

"However I need this to recalculate so it only counts the visable cells"

need more clarification

Punnam

When i apply a filter i only want it to count the cells i have filtered to.

Hi,

Someone else should help you , i think can be done with the help of offset and sumproduct .

Punnam

Try

SUMPRODUCT((B2:B30="Y")*(A2:A30="M")*(SUBTOTAL(3,OFFSET(B2,ROW(2:30)-2,))))