Counting multiple criteria AND with additional OR

Discuss the spreadsheet application
Post Reply
drhalter
Posts: 13
Joined: Wed Mar 05, 2014 9:33 pm

Counting multiple criteria AND with additional OR

Post by drhalter »

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
Sample data set
SampleDataSet.ods
SampleDataSet
(26.93 KiB) Downloaded 93 times
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting multiple criteria AND with additional OR

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
drhalter
Posts: 13
Joined: Wed Mar 05, 2014 9:33 pm

Re: Counting multiple criteria AND with additional OR

Post by drhalter »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting multiple criteria AND with additional OR

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Counting multiple criteria AND with additional OR

Post by robleyd »

In case you need it - How to update your signature
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply