Counting duplicates - multiple conditions

[Excel_beginner_newb]

Hi everyone,

I need help writing a formula for our laboratory work. In essence, our log indicates this:

Column A: Subject # (unique identifier)
Column B: Status (x if deceased, blank if alive)
Column C: Location Number

Basically, there can be multiple subjects in one location. I would like to write a formula that will tell us how many locations are currently operating. Firstly, the formula only accounts for living subjects (ie. their cell is BLANK in column B). Then, I need to sum up the number of unique locations from column C (ignoring duplicates). Column A is not relevant at this moment. All the data starts in row 2. Row 1 is my headers, and I am trying to write the formula in D1 and have floating headers so it is easy to keep track.

I tried the following formula:=IF(ISBLANK($B:$B),SUM(IF(FREQUENCY($C:$C,$C:$C)>0,1)))

But it counts all the subjects, even those with an "x" in corresponding cell B. Can someone help me with my formula? I think I need a way to match the conditions? Meaning, only if both B is blank, and C has a value, then sum up the number of unique cells from C.

Thank you!

 

[Aladin Akyurek]

Assuming that the location values are true numbers...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B:B="",C:C),C:C),1))

 

[Excel_beginner_newb]

Assuming that the location values are true numbers...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B:B="",C:C),C:C),1))

My friend, you are truly amazing. If you don't mind, can you expand on a few questions for me?

What was the issue with my formula? How does Ctrl+Shift+Enter differ from just Enter? And lastly, is there a way I can make the formula IGNORE blanks in C (but still recognize blanks in B)?

Thank you so much!

 

[Excel_beginner_newb]

I am testing the formula =SUM(IF(FREQUENCY(IF(B:B="",C:C),C:C),1)) by adding some new data to test. It seems that the formula isn't always consistent for some reason? In one worksheet, it is accurate, but in another it is not.

In worksheet 1, when I simply add in a new value for the location, the formula does not update. But if I do this same procedure in worksheet 2, the value updates. What might be causing this?

 

[XOR LX]

=IF(ISBLANK($B:$B),SUM(IF(FREQUENCY($C:$C,$C:$C)>0,1)))

=SUM(IF(FREQUENCY(IF(B:B="",C:C),C:C),1))

You should both know that, unless you happen to have data extending all the way down to row one million-plus, the use of entire column references within an array formula is not at all a good idea.

For example, if, in reality, you only have data extending as far as row 1000, then, by using entire column references, you are forcing Excel to calculate more than one million cells than are actually necessary, an astonishingly poor efficiency.

Try choosing a suitably low - though sufficient - value for the upper row reference. Even better, make your ranges dynamic, such that the upper row reference is dynamically determined via a formula, thus offering optimal efficiency.

Regards

 

[Aladin Akyurek]

My friend, you are truly amazing. If you don't mind, can you expand on a few questions for me?

What was the issue with my formula? How does Ctrl+Shift+Enter differ from just Enter? And lastly, is there a way I can make the formula IGNORE blanks in C (but still recognize blanks in B)?

Thank you so much!

The IF call as part of the formula necessitates control+shift+enter.

I am testing the formula =SUM(IF(FREQUENCY(IF(B:B="",C:C),C:C),1)) by adding some new data to test. It seems that the formula isn't always consistent for some reason? In one worksheet, it is accurate, but in another it is not.

In worksheet 1, when I simply add in a new value for the location, the formula does not update. But if I do this same procedure in worksheet 2, the value updates. What might be causing this?

The whole references might be the cause of a delayed update.

Let's take a different route...

In A1 enter:

=MATCH(9.99999999999999E+307,C:C)

In A2 control+shift+enter:

=SUM(IF(FREQUENCY(IF(B1:INDEX(B:B,A1)="",C1:INDEX(C:C,A1)),C1:INDEX(C:C,A1)),1))