Count distinct rows with multiple criteria

[keelaboosa]

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?

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]COMPOUND
[/TD]
[TD]CONTAINER VOLUME
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]100
[/TD]
[/TR]
</tbody>[/TABLE]

 

[keelaboosa]

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.

 

[SpillerBD]

"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.

 

[keelaboosa]

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.

 

[Marcelo Branco]

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.

 

[keelaboosa]

Marcelo, thanks for the suggestion. I get 0, not 1.

 

[Marcelo Branco]

Marcelo, thanks for the suggestion. I get 0, not 1.

It worked for me. Have you confirmed the formula with Ctrl+Shift+Enter simultaneously, not just Enter?

M.

 

[Marcelo Branco]

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

COMPOUND​

[/td][td]

CONTAINER VOLUME​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

40​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

B​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

C​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

D​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

E​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

F​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

A​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

B​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

C​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

D​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

E​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

G​

[/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

A​

[/td][td]

100​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

H​

[/td][td]

100​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

D​

[/td][td]

100​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

E​

[/td][td]

100​

[/td][td][/td][/tr]
[/table]

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.

 

[keelaboosa]

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?

 

[markmzz]

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)),"")


[TABLE="class: grid, width: 318"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]COMPOUND[/TD]
[TD]CONTAINER VOLUME[/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]E[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]G[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]A[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]H[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]E[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]**[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[TD]******[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz