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]

 

[Marcelo Branco]

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?

Great!

This a well known formula to count unique values.
To see what the formula does, step by step, try
Formulas > Evaluate formula

To an excellent explanation how to use FREQUENCY function to count unique, see this (a long video ~50 minutes )
https://www.youtube.com/watch?v=uUrI8hoj8BA&feature=relmfu

M.

 

[keelaboosa]

Okay, it appears SUM(IF(FREQUENCY is one of those great tools - just not quite grokking it yet.

I'd like to add one more condition to the test. I've got flow for the compounds as well. I'd like to search for compounds that have no container but do have a flow.

In the table below, FLOW1 should report 0 since the only distinct compound with a zero container (F) also has a zero flow. FLOW2 should report 1 because F does have flow.

I tried the original formula, but added another IF statement (bold) to make sure I only check against values that have a flow greater than zero.

{=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A17,IF(B2:B17>0,IF(C2:C17>0,A2:A17)),0)),MATCH(A2:A17,A2:A17,0)),ROW(A2:A17)-ROW(A2)+1),1))}

It's not working, though; I get 1 for both FLOW1 and FLOW2. I'm sure it's an easy fix, just not sure what it is.
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]COMPOUND[/TD]
[TD]CONT. VOL[/TD]
[TD]FLOW1[/TD]
[TD]FLOW2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]40[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]135[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Okay, it appears SUM(IF(FREQUENCY is one of those great tools - just not quite grokking it yet.

I'd like to add one more condition to the test. I've got flow for the compounds as well. I'd like to search for compounds that have no container but do have a flow.

Try

Flow1 (C2:C17)
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A17,IF(B2:B17>0,A2:A17),0)),IF(C2:C17>0,MATCH(A2:A17,A2:A17,0))),ROW(A2:A17)-ROW(A2)+1),1))
Crtl+Shift+Enter

Flow2 (D2:D17)
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A17,IF(B2:B17>0,A2:A17),0)),IF(D2:D17>0,MATCH(A2:A17,A2:A17,0))),ROW(A2:A17)-ROW(A2)+1),1))
Crtl+Shift+Enter

M.

 

[keelaboosa]

Marcelo, thank you so much! I love coming across concepts in Excel I've never learned. This is right up there with INDEX MATCH.