Hi,
I am working with on trying to come up with a formula that will allow me to use SUMPRODUCT to find the count of rows of data that meet two different criteria. The reason I want to use SUMPRODUCT as opposed to COUNTIFS is because of the need to specify two different criteria (as in this or that) for the two different checks that the formula will do. For example, let’s say I have a two column table. The first column would have a mix of the following: apple, banana, pear and orange. The second column would be a variable length string of quantities for each (i.e. “1”, “2”, “2, 3”, etc). What I want the SUMPRODUCT to be able to do is find the count of all rows that contain either “apple” or “banana” in the first column and either 1 or 2 somewhere in the string in the second column.
Having multiple this or that with SUMPRODUCT

 2StarLounger
 Posts: 114
 Joined: 14 Aug 2019, 00:12

 Administrator
 Posts: 79521
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Having multiple this or that with SUMPRODUCT
It could be something like
=SUMPRODUCT(((ColumnA="apple")+(ColumnA="banana"))*(ISNUMBER(SEARCH("1", ColumnB))+ISNUMBER(SEARCH("2", ColumnB))>0))
=SUMPRODUCT(((ColumnA="apple")+(ColumnA="banana"))*(ISNUMBER(SEARCH("1", ColumnB))+ISNUMBER(SEARCH("2", ColumnB))>0))
Best wishes,
Hans
Hans

 2StarLounger
 Posts: 114
 Joined: 14 Aug 2019, 00:12
Re: Having multiple this or that with SUMPRODUCT
That formula works, counting every row where Column A is either Apple or Banana and the string in Column B contains either 1 or 2; however, where it breaks is rows where Column B contains both. If Column A contains either Apple or Banana and Column B contains either 1 or 2, the formula counts the row once. If Column B contains both, for example "1, 2", the formula counts the row twice. What the formula should do is count the row only once in that it contains either 1, 2, or both 1 and 2 in Column B and either Apple or Banana in Column A.

 Administrator
 Posts: 79521
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Having multiple this or that with SUMPRODUCT
Are you sure you used the formula correctly? I took care to count rows that contain "apple" or "banana" and both "1" and "2" just once:
The >0 near the end is essential.
The >0 near the end is essential.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans

 2StarLounger
 Posts: 114
 Joined: 14 Aug 2019, 00:12
Re: Having multiple this or that with SUMPRODUCT
Ahhh I see it now! I was missing that >0