Frequency IF <>"value" counts also blank cells

[Michaela K]

Hello,

I'm trying to create frequencies table that will calculate frequency at baseline, during treatment and during follow-up. (please see snapshot below).

The formula is as follows in the "Frequency at Baseline" and works great:

=FREQUENCY(IF('Vitals - baseline_dosing_FU'!E:E = "Single Dose-D1",'Vitals - baseline_dosing_FU'!P:P), Vitals_Frequencies!B2:B7),

But, when I change the equal sign to <>, it also counts blank cells.

I've tried AND(NOT(ISBLANK) but I still don't seem to be getting what I want as, for a change, I'm getting everything as 0, which is not true:

=FREQUENCY(IF(AND(NOT(ISBLANK('Vitals - baseline_dosing_FU'!E:E)), 'Vitals - baseline_dosing_FU'!E:E <> "Single Dose-D1"), 'Vitals - baseline_dosing_FU'!P:P, ""), Vitals_Frequencies!B2:B7).

What I want to say is that I want the function to only count those frequencies where !E:E <> "Single Dose-D1" but not count blank cells.

Unfortunately, I don't seem to be getting the syntax right and in all honesty, I'm not a pro when it comes to excel formulas.

Can anyone advise?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=FREQUENCY(IF(('Vitals - baseline_dosing_FU'!E:E<>"Single Dose-D1")*('Vitals - baseline_dosing_FU'!E:E<>""),'Vitals - baseline_dosing_FU'!P:P), Vitals_Frequencies!B2:B7)

 

[Michaela K]

Hi & welcome to MrExcel.
How about

=FREQUENCY(IF(('Vitals - baseline_dosing_FU'!E:E<>"Single Dose-D1")*('Vitals - baseline_dosing_FU'!E:E<>""),'Vitals - baseline_dosing_FU'!P:P), Vitals_Frequencies!B2:B7)

Thank you, this works! Question - if I wanted to add another argument to it, so basically not to count also cells that contain "SFU", how would that work? I want to exclude those cells from frequency count that contain "Single Dose-D1" and "SFU"

 

[Fluff]

Try

=FREQUENCY(IF(('Vitals - baseline_dosing_FU'!E:E<>"Single Dose-D1")*('Vitals - baseline_dosing_FU'!E:E<>"sfu")*('Vitals - baseline_dosing_FU'!E:E<>""),'Vitals - baseline_dosing_FU'!P:P), Vitals_Frequencies!B2:B7)

 

[Michaela K]

Try

=FREQUENCY(IF(('Vitals - baseline_dosing_FU'!E:E<>"Single Dose-D1")*('Vitals - baseline_dosing_FU'!E:E<>"sfu")*('Vitals - baseline_dosing_FU'!E:E<>""),'Vitals - baseline_dosing_FU'!P:P), Vitals_Frequencies!B2:B7)

Thank you, I'm getting a result of '1' for values up to limit/bin 59 because one row in the P:P is blank so it counts it as <60. Can I somehow teach to it not to count cells where P:P is blank too?

 

[Fluff]

Try adding that criteria in the same manner as did for the other two requirements.

 

[Michaela K]

Try adding that criteria in the same manner as did for the other two requirements.

Awesome, I think I'm getting the idea. So basically the '*' is replacing AND function, correct?

 

[Fluff]

That's right.