Counting Values Separated by Comma in Frequency/Sum Array Formula

[Amosbroker]

Good morning, everyone!

I have a frequency/sum formula that looks at individuals who meet multiple criteria and then provides a unique number of those individuals. For example, 72 line items match my criteria but only 63 are unique users based off the member ID field. I am running in to a problem. One of the criteria is that Column O = Completed - my formula is picking up those that read Completed only but not reading the ones that read Completed, Not Complete. I still need the formula to acknowledge this as completed even though it is accompanied by a comma and not complete. How do I incorporate this in to my array formula listed below?

=SUM(IF(FREQUENCY(IF(1-(' Query A Notes'!$F$2:$F$1500=""),IF((' Query A Notes'!$G$2:$G$1500="Quarterly lifestyle coaching")*(' Query A Notes'!$K$2:$K$1500="lifestyle coaching")*(' Query A Notes'!$O$2:$O$1500="Completed"),MATCH(' Query A Notes'!$F$2:$F$1500,' Query A Notes'!$F$2:$F$1500,0))),ROW(' Query A Notes'!$F$2:$F$1500)-ROW(' Query A Notes'!$F$2)+1),1))


Thanks a ton!
Angela

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(' Query A Notes'!$F$2:$F$1500=""),IF((' Query A Notes'!$G$2:$G$1500="Quarterly lifestyle coaching")*(' Query A Notes'!$K$2:$K$1500="lifestyle coaching")*ISNUMBER(SEARCH("Completed",' Query A Notes'!$O$2:$O$1500)),MATCH(' Query A Notes'!$F$2:$F$1500,' Query A Notes'!$F$2:$F$1500,0))),ROW(' Query A Notes'!$F$2:$F$1500)-ROW(' Query A Notes'!$F$2)+1),1))

 

[Amosbroker]

Thank you!!

I have a similar formula but I am not applying the unique count/frequency.

=COUNTIFS(' Query A Notes'!$K:$K,"Lifestyle coaching",' Query A Notes'!$N:$N,"In person/onsite",' Query A Notes'!$O:$O,"Completed",' Query A Notes'!$G:$G,"quarterly lifestyle coaching")

Same logic, I am trying to acknowledge complete even when accompanied by comma and not complete.

My formula below doesn't work - how would I edit it to work properly?

=COUNTIFS(' Query A Notes'!$K:$K,"Lifestyle coaching",' Query A Notes'!$N:$N,"In person/onsite",'*ISNUMBER(SEARCH("Completed",' Query A Notes'!$O$2:$O$1500,' Query A Notes'!$G:$G,"quarterly lifestyle coaching")

 

[Aladin Akyurek]

Try...

=COUNTIFS(' Query A Notes'!$K:$K,"Lifestyle coaching",' Query A Notes'!$N:$N,"In person/onsite",' Query A Notes'!$O$2:$O$1500,”*completed*”,' Query A Notes'!$G:$G,"quarterly lifestyle coaching")

 

[Amosbroker]

I get:

[TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl63, width: 125, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

I get:

[TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl63, width: 125, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

Adjust all the ranges to the same size (K2:K1500, N2:N1500, O2:O1500,G2:G1500)

M.

 

[Amosbroker]

Perfect! That was it!! Thank you!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Aladin Akyurek]

I get:

[TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl63, width: 125, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

I failed to notice that whole column references were mixed with a definite range. By the way, are you not implementing dynamic named ranges?

 

[Amosbroker]

Hi Aladin!

I have a second workbook with dynamic ranges that I am working on but keep getting an error. I am trying figure out the solution on my own so that I will be able to troubleshoot and continue to build within the workbook. My goal is to have a workbook where the ranges are dynamic, I had a deadline and the formula above has worked in the meantime to get me by.

If I do not have it figured out in next day or so, I will be reaching out for further assistance. You have no idea how appreciative I am for your help!! Thanks again!