Frequency Formula - Meeting multiple criteria

[Amosbroker]

Ok - usually I can search these message boards or find a YouTube video, it's just not happening for me today. I am looking to count unique ID's in one column that meet criteria in three other columns.

Column G = "" (blank)
Column O = Completed
Column L - Coaching
Column F contains my ID's I want unique total for (there are duplicates and I only want unique/engaged)

I would like to count the unique number of ID's who meet criteria listed above.
My frequency formula keeps giving me error. Well.... my attempt at frequency formula keeps giving me error. I would be forever grateful for any advice/assistance!!!

Thanks a ton!
Angela

 

[Amosbroker]

I used the formula you suggested above:

=SUM(IF(FREQUENCY(IF(1-(F2:F400=""),IF(G2:G400="",IF(L2:L400="coaching",IF(O2:O400="completed",MATCH(F2:F400,F2:F400,0))))),ROW(F2:F400)-ROW(F2)+1),1))

The sheet name: QueryA (this is referenced in my actual formula)
The data is pulled on to a dashboard type separate tab.
Range Name: Notes

 

[Aladin Akyurek]

I used the formula you suggested above:

=SUM(IF(FREQUENCY(IF(1-(F2:F400=""),IF(G2:G400="",IF(L2:L400="coaching",IF(O2:O400="completed",MATCH(F2:F400,F2:F400,0))))),ROW(F2:F400)-ROW(F2)+1),1))

The sheet name: QueryA (this is referenced in my actual formula)
The data is pulled on to a dashboard type separate tab.
Range Name: Notes

You seem to have date/times values. In which column are these located?

 

[Amosbroker]

B and D columns have time/dates but I do not need to take these in to account. I only need the number of unique Member ID’s who are engaged = meet criteria listed in original post. The formula you provided, worked. I’m just trying to figure out a way to easily copy/paste data weekly to refresh the number. The export data file I copy/paste will have the correct time frame of data I need. With the array formula, it’s my understanding I’m confined to a set of data short of dynamic range in specific formula. I just do not know how to apply to my current formula. For example, this week - I may have 600 lines of data but next week, I will have 1342. My column headers will always be the same, in the order and my criteria will be consistent.

 

[Aladin Akyurek]

B and D columns have time/dates but I do not need to take these in to account. I only need the number of unique Member ID’s who are engaged = meet criteria listed in original post. The formula you provided, worked. I’m just trying to figure out a way to easily copy/paste data weekly to refresh the number. The export data file I copy/paste will have the correct time frame of data I need. With the array formula, it’s my understanding I’m confined to a set of data short of dynamic range in specific formula. I just do not know how to apply to my current formula. For example, this week - I may have 600 lines of data but next week, I will have 1342. My column headers will always be the same, in the order and my criteria will be consistent.

Don't worry. We need one of these for creating a dynamic named range.

Define Lrow in the Name Manager as referring to:

=MATCH(9.99999999999999E+307,QueryA!$B:$B)

Define Frange in the Name Manager as referring to:

=QueryA!$F$2:INDEX(QueryA!$F:$F,Lrow)

Define Grange in the Name Manager as referring to:

=QueryA!$G$2:INDEX(QueryA!$G:$G,Lrow)

Define Lrange in the Name Manager as referring to:

=QueryA!$L$2:INDEX(QueryA!$L:$L,Lrow)

Define Orange in the Name Manager as referring to:

=QueryA!$O$2:INDEX(QueryA!$O:$O,Lrow)

Also define Ivec as referring to:

=ROW(Frange)-ROW(INDEX(Frange,1,1))+1

Now delete and paste should work.

The formula now becomes: Control+shift+enter...

=SUM(IF(FREQUENCY(IF(1-(Frange=""),IF(Grange="",IF(Lrange="coaching",IF(Orange="completed",MATCH(Frange,Frange,0))))),Ivec),1))

 

[Amosbroker]

Hi Aladin!

OK - I used the formula and just replaced 400 with 500 throughout my my formula to grab all data. Ok one more question - for column L ( K in real formula - see below), I specified coaching as part of my criteria. I noticed that some fields have more than one type, see example data - it says "coaching, physician referral". Most will say only coaching - How did I pick up that coaching when it is included with physician referral separated by comma? I pasted my entire formula below. I wish I could send you a box of cookies!! You have been so helpful! I am SUPER appreciative! Also, the formula below is correct - I may have referred to the wrong cell or range up above because I was typing from my phone and did not have access to actual file.

=SUM(IF(FREQUENCY(IF(1-(' Query A Notes'!F2:F500=""),IF((' Query A Notes'!G2:G500="")*(' Query A Notes'!K2:K500="coaching")*(' Query A Notes'!O2:O500="Completed"),MATCH(' Query A Notes'!F2:F500,' Query A Notes'!F2:F500,0))),ROW(' Query A Notes'!F2:F500)-ROW(' Query A Notes'!F2)+1),1))

 

[Amosbroker]

I must have posted at the same time as your reply! I am naming the dynamic ranges now!

 

[Amosbroker]

Hmmm - I am getting an NA -

Here is what I have:
Frange =QueryA!$F$2:INDEX(QueryA!$F:$F,Lrow)
Grange =QueryA!$G$2:INDEX(QueryA!$G:$G,Lrow)
Ivec =ROW(Frange)-ROW(INDEX(Frange,1,1))+1
Krange =QueryA!$K$2:INDEX(QueryA!$K:$K,Lrow)
Lrow =MATCH(9.99999999999999E+307,QueryA!$B:$B)
Orange =MATCH(9.99999999999999E+307,QueryA!$B:$B)

This is the formula I am using =SUM(IF(FREQUENCY(IF(1-(Frange=""),IF(Grange="",IF(Krange="coaching",IF(Orange="completed",MATCH(Frange,Frange,0))))),Ivec),1))

The only adjustment I made was to change L to K due to the data actually be one more row over.

I am using control shift enter at the end of the formula as well.

 

[Aladin Akyurek]

You need:

Orange = QueryA!$O$2:INDEX(Query!$O:$O,Lrow)

If still #N/A, what do you get when you do the following test?

=Lrow

 

[Amosbroker]

Orange is =QueryA!$O$2:INDEX(QueryA!$O:$O,Lrow) (I made a mistake when copying ranges over.)

I get this when I click =Lrow

#N/A

 

[Aladin Akyurek]

Orange is =QueryA!$O$2:INDEX(QueryA!$O:$O,Lrow) (I made a mistake when copying ranges over.)

I get this when I click =Lrow

#N/A

Don't you have date/times values in QueryA!$B:$B?