Count distinct rows with multiple criteria

keelaboosa

New Member
Joined
Apr 3, 2018
Messages
35
I'm having a hard time with this, and I'd really appreciate some help.

I've got 8 compounds distributed across 4 containers. I need to see if there are any compounds for which the container is empty.

I can use =SUMPRODUCT(1/COUNTIFS(tbl[COMPOUND],tbl[COMPOUND])) to return number of distinct compounds, but I need to include a test for CONTAINER VOLUME too.

In the table below, compound F is the only distinct compound with a container volume of zero - I should get a count of 1. How would I write this formula?

COMPOUND
CONTAINER VOLUME
A
40
B
C
D
E
F
A
135
B
135
C
135
D
135
E
135
G
135
A
100
H
100
D
100
E
100

<tbody>
</tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I wish I could edit the post. I mean to say, I need to see if there are any compounds assigned ONLY to containers with zero volume.
 
Upvote 0
"I need to see if there are any compounds for which the container is empty."
I wouldn't bother with any formula. A simple Pivot Table would answer this question.
You can get a Distinct Count option if you use the Data Model for a Pivot Table as well.
 
Upvote 0
Thanks for the quick reply!

I don't want the user to have to remember to hit refresh when they've changed a value affecting the container volume, so I'd rather use a formula.
 
Upvote 0
I wish I could edit the post. I mean to say, I need to see if there are any compounds assigned ONLY to containers with zero volume.

Try this array formula
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A17,IF(B2:B17<>"",A2:A17),0)),MATCH(A2:A17,A2:A17,0)),ROW(A2:A17)-ROW(A2)+1),1))
Ctrl+Shift+Enter

M.
 
Upvote 0

A
B
C
1
COMPOUND​
CONTAINER VOLUME​
Result​
2
A​
40​
1​
3
B​
4
C​
5
D​
6
E​
7
F​
8
A​
135​
9
B​
135​
10
C​
135​
11
D​
135​
12
E​
135​
13
G​
135​
14
A​
100​
15
H​
100​
16
D​
100​
17
E​
100​

Array formula in C2
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A17,IF(B2:B17<>"",A2:A17),0)),MATCH(A2:A17,A2:A17,0)),ROW(A2:A17)-ROW(A2)+1),1))
Ctrl+Shift+Enter

M.
 
Upvote 0
Got it! When I typed my table into the post, it replaced zeros with blanks. I changed your formula from B2:B17<>"" to B2:B17>0 and everything works great.

How on earth did you get your mind to wrap around these array formulas? :eek:
 
Upvote 0
Hi try this too:

In D2 and copy down (Array Formula - use Ctrl+Shift+Enter to enter the formula)

=IFERROR(INDEX(A$2:A$17,MATCH(0,COUNTIF(D$1:D1,A$2:A$17)+
(SUMIF(A$2:A$17,A$2:A$17,B$2:B$17)<>0),0)),"")

Or this (use only Enter to the formula)

=IFERROR(INDEX(A$2:A$17,MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$17)+
(SUMIF(A$2:A$17,A$2:A$17,B$2:B$17)<>0),),0)),"")



ABCDE
1COMPOUNDCONTAINER VOLUMEResult
2A40D
3BF
4C
5D
6E
7F
8A135
9B135
10C135
11D
12E135
13G135
14A100
15H100
16D
17E100
18
********************************

<tbody>
</tbody>

Markmzz
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top