Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
sumif #value | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |