Counting multiple criteria AND with additional OR

Discuss the spreadsheet application

Counting multiple criteria AND with additional OR

Postby drhalter » 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.

SampleDataSet.png
Sample data set

SampleDataSet.ods
SampleDataSet
(26.93 KiB) Downloaded 15 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
drhalter
 
Posts: 13
Joined: Wed Mar 05, 2014 9:33 pm

Re: Counting multiple criteria AND with additional OR

Postby Villeroy » Wed May 16, 2018 1:55 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25258
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting multiple criteria AND with additional OR

Postby drhalter » Wed May 16, 2018 4:09 pm

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

Postby Villeroy » Wed May 16, 2018 7:07 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25258
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting multiple criteria AND with additional OR

Postby MrProgrammer » Thu May 17, 2018 7:51 am

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3289
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Counting multiple criteria AND with additional OR

Postby robleyd » Thu May 17, 2018 7:58 am

In case you need it - How to update your signature
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1584
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: No registered users and 26 guests