Excel - count values in a range of cells depending on value in adjacent ce

Asked By birdgirl3 on 17-Nov-08 06:41 PM
I have a spreadsheet with names in column A and names are often repeated
within this column. Columns B - E have values. I want to count the valuies in
each column only if the name in the row is ""Bob"?

I also sometimes want to count the values in each column if the name is




T. Valko replied on 17-Nov-08 06:55 PM
values = numbers?

Try these:

=SUMPRODUCT((A1:A10="Bob")*(ISNUMBER(B1:E10)))

=SUMPRODUCT(((A1:A10="Bob")+(A1:A10="Dave"))*(ISNUMBER(B1:E10)))

Better to use cells to hold the criteria:

G1 = Bob
H1 = Dave

=SUMPRODUCT((A1:A10=G1)*(ISNUMBER(B1:E10)))

=SUMPRODUCT(((A1:A10=G1)+(A1:A10=H1))*(ISNUMBER(B1:E10)))


--
Biff
Microsoft Excel MVP