Page 1 of 1

Counting multiple criteria AND with additional OR

PostPosted: Wed May 16, 2018 1:27 pm
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.

SampleDataSet.png
Sample data set

SampleDataSet.ods
SampleDataSet
(26.93 KiB) Downloaded 45 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

Re: Counting multiple criteria AND with additional OR

PostPosted: Wed May 16, 2018 1:55 pm
by Villeroy
This is a job for a database.

Re: Counting multiple criteria AND with additional OR

PostPosted: Wed May 16, 2018 4:09 pm
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?

Re: Counting multiple criteria AND with additional OR

PostPosted: Wed May 16, 2018 7:07 pm
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.

Re: Counting multiple criteria AND with additional OR

PostPosted: Thu May 17, 2018 7:51 am
by MrProgrammer
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.

Re: Counting multiple criteria AND with additional OR

PostPosted: Thu May 17, 2018 7:58 am
by robleyd
In case you need it - How to update your signature