Nested Formula

R

Rick

In a table below, I am trying to "Count" the number of time a row
begining with "H" has a number "1" iin it. I believe this has to be a
"Nested" function but I am unsure.

I will repeat this function for the leters A/U/S, and for the number
0/2 as well.


H 1 1 0 2 0
H 2 2 1 0 0
A 1 1 1 1 1
U 0 0
U 2 1 1
S 0
H 1 2 1 2 1
A 2 2 2 1 1
U 1 1 1 1 2


Rick
 
F

Frank Kabel

Hi Rick
try the following for counting all H/1 in your range:
=SUMPRODUCT((A1:A999="H")*(B1:H999=1))
assumption: Your range extends from A1 to H999

HTH
Frank
 
P

Paul

This may or may not be what Rick wanted. Consider the first row of his
sample data:
H 1 1 0 2 0
This will count as 2 in your formula.
It depends whether he wants to count the number of 1's in rows beginning
with H, or the number of rows beginning with H that also contain a 1
(anywhere).
 
F

Frank Kabel

Hi Paul

you're definetly right. If Rick only wants to count all rows beginning
with 'H' which contain at least on '1' my function won't work. One
solution could be using the following:
=SUMPRODUCT((A1:A999="H")*(((B1:B999=1)+(C1:C999=1)+(D1:D999=1)+(E1:E99
9=1)+(F1:F999=1))>0))
This is of course clumsy. I think Harlan Grove posted a better solution
for this case.

Frank
 
Top