sumif and or

[Guest]

I have an input sheet which contains the data and a summary sheet. I need to
put a formula in the summary sheet to count the number of rows in which the
following may occur "PPAO" is in Column A and the number 1 is in either
columns B or C or D.
Any help appreciated.

 

[Guest]

The following works for me

=SUM(SUMIF(A1:A26,"PPAO",B1:B26),SUMIF(A1:A26,"PPAO",C1:C26),SUMIF(A1:A26,"PPAO",D126))

HTH

 

[Bob Phillips]

=SUMPRODUCT(--(Input!A2:A200="PPAO"),--((Input!B2:B200=1)+(Input!C2:C200=1)+(Input!D2200=1)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks for the reply but unfortunately this did not give me the result I was
expecting. Also I will need to do a similar formula in another cell to count
the same data except this time if the number 2 is in columns B or C or D.

 

[Guest]

Jimar,

With a macro perhaps:-

Sub liminal()
Dim myRange As Range
lastrowcola = Range("A65536").End(xlUp).Row
Set myRange = Range("A1:A" & lastrowcola)
For Each c In myRange
c.Select
If c.Value = "PPAO" Then
If ActiveCell.Offset(0, 1).Value = 1 _
Or ActiveCell.Offset(0, 2).Value = 1 _
Or ActiveCell.Offset(0, 3).Value = 1 Then
Count = Count + 1
End If
End If
Next
Range("E5").Value = Count
End Sub

Mike

 

[Guest]

Thanks Bob but this is adding together the number 1s when they appear along
with PPAO. What I need to count is the number of rows that contain PPAO when
there is a number 1 in either col B or C or D. So if row 6 has PPAO in Col A
and the number 1 in Col B and number 1 is also in C the answer to the formula
should be 1 (not 2).

 

[Guest]

Using a helper column eg Col G Cells G2:G12

Formula for G2
=IF(OR(B2=1,C2=1,D2=1)*AND(A2="PPAO"),1,0)

Then using the COUNTIF to get the final solution
=COUNTIF(G2:G13,1)

Not sure if this is an extra step that you are willing to take?
Only other option that I can think of is using a macro as Mike H suggested

HTH

 

[Bob Phillips]

Sorry about that

=SUMPRODUCT(--(Input!A2:A20="PPAO"),--(((Input!B2:B20=1)+(Input!C2:C20=1)+(Input!D220=1)<>0)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you Bob. This worked perfectly.

Sorry about that

=SUMPRODUCT(--(Input!A2:A20="PPAO"),--(((Input!B2:B20=1)+(Input!C2:C20=1)+(Input!D220=1)<>0)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)