### Counting multiple criteria AND with additional OR

Posted:

**Wed May 16, 2018 1:27 pm**Count every Row where PersonX appears in column A AND one product of (Product1 OR Product2 OR … OR ProductN) appears in Column C.

Hello: Thanks for reviewing! Hopefully enough detail and not too much!

I have a larger set of data from which I need to prepare a report. That data has two columns from which I'll pull to summarize the data into a report. These are represented below. Two columns have people, the third has product ID codes. One person might have multiple products and each product can have multiple people. Each person can be associated with the same product on different rows, such that each row should be counted (you can think of each row as an occurrence on different dates, which it is). In any given row, a person can only be associated with the product once, either Primary or Secondary (or Tertiary or …).

Also, I'm required to report the data in groups. So person A, we will call him Jones, might have in Grouping 1, products 49585, 49580, and 49505. Group 2 might be 21000 and 21005. Grouping 3 might be just 49000 without any others. Here's a sample data set with groupings and the Report shown as well. Colors help to visualize the groupings.

Now, I've written the following for group 1 (with the appropriate similar equation for group 2, group 3, Secondary cases)

Group 1 =SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49585)+SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49580)+SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49505)

Or, one of many other solutions, thanks to MrProgrammer's 201710302050.ods

=SUMPRODUCT(A2:A15=”Jones”;SIGN((C2:C15=49585)+(C2:C15=49580)+(C2:C15=49505)))

And similar for Group 2 and for Group 3 … That's great you say, you've answered your own question! But not really, because, I'm not just dealing with 3 groups, I'm dealing with almost 200 groupings. And I'm not dealing with just 6 products, but up to 6,000 or 7,000 total products, from 1 to 50 in a grouping. So, you see, it would be great to get this to a generalized solution.

So, with the current solutions, I would need to duplicate the repetitive parts of the equation up to 50 times, and then change each equation manually if for some reason the groupings changed. Is there a way to write this sort of formula such that it is more easily expandable based on a criteria list posted elsewhere in the worksheet? To write this in another way, I want to count every row once where PersonX is in column A and where column C contains products as specified in another column (or row) Product1 OR Product2 OR Product3 OR Product4 OR … OR ProductN (where the product numbers vary and the number of products in this list is from 1 to N) and then another where PersonX is in column B

One thing I did see from MrProgrammer as well was this:

=SUMPRODUCT(C2:C15={49585;49580;49505})

This works but gives me everybody (8), not just “Jones.” If I try to add the criteria for “Jones” like this: =SUMPRODUCT(A2:A15=“Jones”;C2:C15={49585;49580;49505}) I get the error #VALUE... If I use criteria {49585|49580|49505} I get the answer 0 …

Looking through some Excel websites, I came across this sort of equation (I think I adapted correctly for OpenOffice).

=SUMPRODUCT(ISNUMBER(MATCH(“Jones”;A2:A15;0))*ISNUMBER(MATCH({49585|49580|49505};C2:C15;0)))

This looks good in the Excel documentation (https://exceljet.net/formula/sumproduct ... r-criteria), but I get the wrong answer in my very short example, 3 instead of 4 – doesn't seem to matter if I use the | or ; to separate the specified product code numbers. I'm not sure why it should work in Excel, either, since Match should give you the first instance of whatever criteria everytime... Anyways, we don't need to fix Excel, but I thought it might give someone an idea.

My signature is wrong. I'm on Apache OpenOffice 4.1.5 on MacOS 10.13.2

Hello: Thanks for reviewing! Hopefully enough detail and not too much!

I have a larger set of data from which I need to prepare a report. That data has two columns from which I'll pull to summarize the data into a report. These are represented below. Two columns have people, the third has product ID codes. One person might have multiple products and each product can have multiple people. Each person can be associated with the same product on different rows, such that each row should be counted (you can think of each row as an occurrence on different dates, which it is). In any given row, a person can only be associated with the product once, either Primary or Secondary (or Tertiary or …).

Also, I'm required to report the data in groups. So person A, we will call him Jones, might have in Grouping 1, products 49585, 49580, and 49505. Group 2 might be 21000 and 21005. Grouping 3 might be just 49000 without any others. Here's a sample data set with groupings and the Report shown as well. Colors help to visualize the groupings.

Now, I've written the following for group 1 (with the appropriate similar equation for group 2, group 3, Secondary cases)

Group 1 =SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49585)+SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49580)+SUMPRODUCT(A2:A15="Jones";$C$2:$C$15=49505)

Or, one of many other solutions, thanks to MrProgrammer's 201710302050.ods

=SUMPRODUCT(A2:A15=”Jones”;SIGN((C2:C15=49585)+(C2:C15=49580)+(C2:C15=49505)))

And similar for Group 2 and for Group 3 … That's great you say, you've answered your own question! But not really, because, I'm not just dealing with 3 groups, I'm dealing with almost 200 groupings. And I'm not dealing with just 6 products, but up to 6,000 or 7,000 total products, from 1 to 50 in a grouping. So, you see, it would be great to get this to a generalized solution.

So, with the current solutions, I would need to duplicate the repetitive parts of the equation up to 50 times, and then change each equation manually if for some reason the groupings changed. Is there a way to write this sort of formula such that it is more easily expandable based on a criteria list posted elsewhere in the worksheet? To write this in another way, I want to count every row once where PersonX is in column A and where column C contains products as specified in another column (or row) Product1 OR Product2 OR Product3 OR Product4 OR … OR ProductN (where the product numbers vary and the number of products in this list is from 1 to N) and then another where PersonX is in column B

One thing I did see from MrProgrammer as well was this:

=SUMPRODUCT(C2:C15={49585;49580;49505})

This works but gives me everybody (8), not just “Jones.” If I try to add the criteria for “Jones” like this: =SUMPRODUCT(A2:A15=“Jones”;C2:C15={49585;49580;49505}) I get the error #VALUE... If I use criteria {49585|49580|49505} I get the answer 0 …

Looking through some Excel websites, I came across this sort of equation (I think I adapted correctly for OpenOffice).

=SUMPRODUCT(ISNUMBER(MATCH(“Jones”;A2:A15;0))*ISNUMBER(MATCH({49585|49580|49505};C2:C15;0)))

This looks good in the Excel documentation (https://exceljet.net/formula/sumproduct ... r-criteria), but I get the wrong answer in my very short example, 3 instead of 4 – doesn't seem to matter if I use the | or ; to separate the specified product code numbers. I'm not sure why it should work in Excel, either, since Match should give you the first instance of whatever criteria everytime... Anyways, we don't need to fix Excel, but I thought it might give someone an idea.

My signature is wrong. I'm on Apache OpenOffice 4.1.5 on MacOS 10.13.2