Nested Formula

  • Thread starter Thread starter Rick
  • Start date Start date
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
 
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
 
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).
 
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
 
Back
Top