sumif and or

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jimar - 21 Jun 2007 10:39 GMT

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.

Reply to this Message

steve_doc - 21 Jun 2007 11:14 GMT

The following works for me

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

HTH

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

Reply to this Message

jimar - 21 Jun 2007 11:27 GMT

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.

> The following works for me
>
[quoted text clipped - 7 lines]
> > columns B or C or D.
> > Any help appreciated.

Reply to this Message

steve_doc - 21 Jun 2007 14:12 GMT

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

> 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
[quoted text clipped - 11 lines]
> > > columns B or C or D.
> > > Any help appreciated.

Reply to this Message

Bob Phillips - 21 Jun 2007 11:17 GMT

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

Signature

HTH

Bob

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

>I have an input sheet which contains the data and a summary sheet. I need
>to
[quoted text clipped - 3 lines]
> columns B or C or D.
> Any help appreciated.

Reply to this Message

jimar - 21 Jun 2007 12:34 GMT

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

> =SUMPRODUCT(--(Input!A2:A200="PPAO"),--((Input!B2:B200=1)+(Input!C2:C200=1)+(Input!D2:D200=1)))
>
[quoted text clipped - 5 lines]
> > columns B or C or D.
> > Any help appreciated.

Reply to this Message

Bob Phillips - 21 Jun 2007 14:46 GMT

Sorry about that

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

Signature

HTH

Bob

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

> Thanks Bob but this is adding together the number 1s when they appear
> along
[quoted text clipped - 17 lines]
>> > columns B or C or D.
>> > Any help appreciated.

Reply to this Message

jimar - 25 Jun 2007 10:06 GMT

Thank you Bob. This worked perfectly.

> Sorry about that
>
[quoted text clipped - 21 lines]
> >> > columns B or C or D.
> >> > Any help appreciated.

Reply to this Message

Mike H - 21 Jun 2007 12:12 GMT

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

Reply to this Message