sumif and or
 

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.

sumif and or
 

The following works for me

=SUM(SUMIF(A1:A26,"PPAO",B1:B26),SUMIF(A1:A26,"PPA O",C1:C26),SUMIF(A1:A26,"PPAO",D1:D26))

HTH

"jimar" wrote:

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.

sumif and or
 

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

--
HTH

Bob

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

"jimar" wrote in message
...
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.

sumif and or
 

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.

"steve_doc" wrote:

The following works for me

=SUM(SUMIF(A1:A26,"PPAO",B1:B26),SUMIF(A1:A26,"PPA O",C1:C26),SUMIF(A1:A26,"PPAO",D1:D26))

HTH

"jimar" wrote:

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.

sumif and or
 

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

"jimar" wrote:

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.

sumif and or
 

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).

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"jimar" wrote in message
...
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.

sumif and or
 

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

"jimar" wrote:

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.

"steve_doc" wrote:

The following works for me

=SUM(SUMIF(A1:A26,"PPAO",B1:B26),SUMIF(A1:A26,"PPA O",C1:C26),SUMIF(A1:A26,"PPAO",D1:D26))

HTH

"jimar" wrote:

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.

sumif and or
 

Sorry about that

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

--
HTH

Bob

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

"jimar" wrote in message
...
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).

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"jimar" wrote in message
...
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.

sumif and or
 

Thank you Bob. This worked perfectly.

"Bob Phillips" wrote:

Sorry about that

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

--
HTH

Bob

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

"jimar" wrote in message
...
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).

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"jimar" wrote in message
...
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.