PDA

View Full Version : Excel Subtotal/ Filter

06-03-2002, 11:27 AM
Column A has list of names- some repeating.
Cloumn B has 'Y' and 'N' only beside each name
If I filter on a name in A I want to total from column B how many Y's there are.

tried Subtotal above the column but cant work it to exclude the N's. have resorted to adding a hidden column next to b and assigning 1 for Y and 0 for N and then subtotalling (sum) of this column- is there a way I can avoid this?

06-03-2002, 12:33 PM
If you also filter Column B as 'Y'
=SUBTOTAL(3,Bm:Bn) will give a count of the Y's.

06-03-2002, 01:26 PM
An alternative is to use an Array Formula which will give a count of the number of Y's for any Name without filtering.
The following example assumes that the particular Name in question has been entered in cell E1, and the formula is in F1.

=SUM(IF(\$A\$1:\$A\$100=\$E1,IF(\$B\$1:\$B\$100='Y',1,0)))

As this is an array formula it must be entered using &lt;Ctrl&gt;&lt;Shift&gt;&lt;Enter&gt;

Expand the row number 100 to be larger than the list.

It follows that if from E1 downwards contains a list of all the individual names and the formula in F1 is copied downwards as well, you will have a dynamic count of the changeing Y's for all names.

HTH