Countif AutoFilter [SOLVED]

[GB]

Hi,

can you help with me solve the following problem please? In column A (range A4:A100)each cell contains either a "Y" or "N". In column B (range B4:B100) each cell contains either the number 1 or " ". When I use the autofilter on column B and select all cells containing 1, I want to count the number of "Y" in cell A1 and the number of "N" in cell A2.


Thanks in advance.

regards

GB

 

[shrivallabha]

In A1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-4,))*(A4:A100="Y"))

and in A2:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-4,))*(A4:A100="N"))


However, SUMPRODUCT can be applied without having to filter and yet to get correct results.

 

[Colin Legg]

Hi,


With the autofilter on, to count the "Y"s:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-ROW(A4),,1))*(A4:A100="Y"))


and to count the "N"s:

[code]=SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-ROW(A4),,1))*(A4:A100="N"))


------


Incidentally, you can do this more easily without applying an autofilter.

To count 1s and "Y"s:

=COUNTIFS(A4:A100,"Y",B4:B100,1)

and 1s and "N"s:

=COUNTIFS(A4:A100,"N",B4:B100,1)[/code]

 

[GB]

Thanks very much, that works perfectly. regards