Forum Home
Press F1
Thread ID: 16341	2002-03-05 23:27:00	Excel Subtotal/ Filter	Guest (0)	Press F1

Post ID	Timestamp	Content	User
38044	2002-03-05 23:27:00	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?	Guest (0)
38045	2002-03-06 00:33:00	If you also filter Column B as 'Y' =SUBTOTAL(3,Bm:Bn) will give a count of the Y's.	Guest (0)
38046	2002-03-06 01:26:00	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 <Ctrl><Shift><Enter> 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	Guest (0)
1