Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count array with OR condition
Hi I have searched this group and not found an answer. I need 1) a count of the number of rows having a 3 in ANY of the three columns 2) where column D contains R A B C D 1 1 3 3 R 2 1 1 2 x 3 2 1 1 x 4 1 3 4 x 5 3 1 3 R The answer should be 3 [row 1,5] because those rows have a 3 in A or B or C AND column D=R I've tried an array formula =SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because the * operator is treated as an AND Is there a different operator that is treated as an OR condition? -- Thanks for your reply & assistance. Jimbo213 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fixed typo: How to count array with OR condition
corrected typo: see corrected post below - added R in cell D3 - formula result should be 2, not 3 "Jimbo213" wrote: Hi I have searched this group and not found an answer. I need 1) a count of the number of rows having a 3 in ANY of the three columns 2) where column D contains R A B C D 1 1 3 3 R 2 1 1 2 x 3 2 1 1 R 4 1 3 4 x 5 3 1 3 R The answer should be 2 [row 1,5] because those rows have a 3 in A or B or C AND column D=R I've tried an array formula =SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because the * operator is treated as an AND Is there a different operator that is treated as an OR condition? -- Thanks for your reply & assistance. Jimbo213 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fixed typo: How to count array with OR condition
=SUM(IF((D1:D5="R")*((A1:A5=3)+(B1:B5=3)+(C1:C5=3) ),1))
ctrl+shift+enter, not just enter "Jimbo213" wrote: corrected typo: see corrected post below - added R in cell D3 - formula result should be 2, not 3 "Jimbo213" wrote: Hi I have searched this group and not found an answer. I need 1) a count of the number of rows having a 3 in ANY of the three columns 2) where column D contains R A B C D 1 1 3 3 R 2 1 1 2 x 3 2 1 1 R 4 1 3 4 x 5 3 1 3 R The answer should be 2 [row 1,5] because those rows have a 3 in A or B or C AND column D=R I've tried an array formula =SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because the * operator is treated as an AND Is there a different operator that is treated as an OR condition? -- Thanks for your reply & assistance. Jimbo213 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count array with OR condition
=sumproduct(sign(0+(a1:a5=3)+(b1:b5=3)+(c1:c5=3)),--(d1:d5="R"))
Regards, Bernd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count array with OR condition
As long as you have less than ~5400 rows of data...
Try this: =SUMPRODUCT(--(MMULT(--(A1:C5=3),{1;1;1})0),--(D1:D5="R")) Or, with this one, no row limit if you're using Excel 2007, 65535 row limit in other versions of Excel: =SUMPRODUCT(--((A1:A5=3)+(B1:B5=3)+(C1:C5=3)0),--(D1:D5="R")) -- Biff Microsoft Excel MVP "Jimbo213" wrote in message ... Hi I have searched this group and not found an answer. I need 1) a count of the number of rows having a 3 in ANY of the three columns 2) where column D contains R A B C D 1 1 3 3 R 2 1 1 2 x 3 2 1 1 x 4 1 3 4 x 5 3 1 3 R The answer should be 3 [row 1,5] because those rows have a 3 in A or B or C AND column D=R I've tried an array formula =SUM((A1:A5=3)*(B1:B5=3)*(C1:C5))*(D1:D5="R")1 but that gets zero because the * operator is treated as an AND Is there a different operator that is treated as an OR condition? -- Thanks for your reply & assistance. Jimbo213 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula That Counts First Value Only In Array As A Condition | Excel Worksheet Functions | |||
Dynamic multiple-condition array function with more than one name | Excel Worksheet Functions | |||
Count occurences in array with condition | Excel Worksheet Functions | |||
How do I sum an array with multiple condition?? | Excel Worksheet Functions | |||
Summing an array based on text condition | Excel Discussion (Misc queries) |