## Counting multiple criteria AND with additional OR

Discuss the spreadsheet application

### Counting multiple criteria AND with additional OR

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.

Sample data set

SampleDataSet.ods
SampleDataSet

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
drhalter
OpenOffice 4.1.3 on MacOS
drhalter

Posts: 13
Joined: Wed Mar 05, 2014 9:33 pm

### Re: Counting multiple criteria AND with additional OR

This is a job for a database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26555
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Counting multiple criteria AND with additional OR

Yes Villeroy. You are quite right. This data is indeed coming as its own report to an Excel file FROM a database. I would love for the database programmer to put this query together for us. In the last 13 years of this database, however, the programmer hasn't done it for some reason. So, that's why I'm trying to use Calc (I don't own Excel, nor do I want to own Excel). Any other ideas?
drhalter
OpenOffice 4.1.3 on MacOS
drhalter

Posts: 13
Joined: Wed Mar 05, 2014 9:33 pm

### Re: Counting multiple criteria AND with additional OR

Connect a Base document to that database and query it. If the database is connectable with read-only access at least, the query may be trivial.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26555
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Counting multiple criteria AND with additional OR

drhalter wrote:My signature is wrong. I'm on Apache OpenOffice 4.1.5 on MacOS 10.13.2
Please correct that and I'll give you a solution using SUMPRODUCT. Thank you for providing the attachment. The level of detail is just right. The picture is not helpful in this situation.

File 201710302050.ods, referenced above, is from [Solved] Conditional counting of cases with disjunction.
Last edited by MrProgrammer on Thu May 17, 2018 8:30 am, edited 2 times in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Volunteer

Posts: 3645
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Counting multiple criteria AND with additional OR

In case you need it - How to update your signature
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2570
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia