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]

Someone - Hellllllllp :/

 

[Marcelo Branco]

Welcome to Mr Excel forum

Could you provide a small data sample along with expected result? 10 rows would be enough.

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

If the ID's are numeric values...

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

If the Id's are not numeric...

=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 latter can be shortened by reducing some of the IF calls...

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

 

[Amosbroker]

You are AWESOME! Thank you so much for the response! Another question...Lets say that I had varying data to drop in each week - could I set my parameters to F2:F1000 to cover the different sets of data data? I could just copy/past new data over old data to get updated numbers? I have to do something additional to my array formula? SUPER thanks AGAIN!!!

 

[Amosbroker]

[TABLE="width: 3540"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]HS[/TD]
[TD]Date[/TD]
[TD]IsComplete[/TD]
[TD]LastUpdateDTm[/TD]
[TD]Group[/TD]
[TD]UserName[/TD]
[TD]RiskStrat[/TD]
[TD]1bsuccessdetails_Question[/TD]
[TD]1dremoved_Question[/TD]
[TD]1WhoCalled_Question[/TD]
[TD]3ContactType_Question[/TD]
[TD]2SessionType_Question[/TD]
[TD]4Audience_Question[/TD]
[TD]5ContactMethod_Question[/TD]
[TD]6SessionOutcome_Question[/TD]
[TD]8messageleft_Question[/TD]
[TD]9additionalattempts_Question[/TD]
[TD]a1successstory_Question[/TD]
[TD]b1TopicsDiscussed_Question[/TD]
[/TR]
[TR]
[TD]5c47f748-48a7-4420-8521-a018b7617bee[/TD]
[TD]1/1/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/2/2018 5:10:32 PM[/TD]
[TD]ABC-11[/TD]
[TD]ABD568[/TD]
[TD][/TD]
[TD]No details[/TD]
[TD][/TD]
[TD]LS[/TD]
[TD]Outbound[/TD]
[TD]Coaching[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]5c47f748-48a7-4420-8521-a018b7617bee[/TD]
[TD]1/2/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/2/2018 4:06:35 PM[/TD]
[TD]ABC-12[/TD]
[TD]ABD569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JB[/TD]
[TD]Inbound[/TD]
[TD]Coaching[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]60864b47-f8a5-4fe0-a7fe-afa154a4bb7a[/TD]
[TD]1/2/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/2/2018 8:24:05 PM[/TD]
[TD]ABC-13[/TD]
[TD]ABD570[/TD]
[TD]HR[/TD]
[TD][/TD]
[TD][/TD]
[TD]JB[/TD]
[TD]Inbound[/TD]
[TD]Coaching[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]5173db51-2e31-46b5-888a-03c15a977892[/TD]
[TD]1/3/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/3/2018 8:18:56 PM[/TD]
[TD]DEF - 23[/TD]
[TD]ABD571[/TD]
[TD][/TD]
[TD]3 days free[/TD]
[TD][/TD]
[TD]JB[/TD]
[TD]Outbound[/TD]
[TD]Coaching[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]fcc91ce3-0831-47fd-9c67-2fa29611d9f5[/TD]
[TD]1/3/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/3/2018 7:53:20 PM[/TD]
[TD]DEF - 24[/TD]
[TD]ABD568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JB[/TD]
[TD]Inbound[/TD]
[TD]Coaching[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]a4dd70dd-229d-4aae-ae0f-422f08722707[/TD]
[TD]1/3/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/3/2018 9:41:40 PM[/TD]
[TD]DEF - 25[/TD]
[TD]ABD568[/TD]
[TD][/TD]
[TD]No progress[/TD]
[TD][/TD]
[TD]JB[/TD]
[TD]Inbound[/TD]
[TD][/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]d41159d5-0fd3-4607-ac78-4b10536fc049[/TD]
[TD]1/3/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/3/2018 9:25:42 PM[/TD]
[TD]DEF - 26[/TD]
[TD]ABD569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LS[/TD]
[TD]Inbound[/TD]
[TD][/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Multiple[/TD]
[/TR]
[TR]
[TD]3488c145-34d1-492a-890b-7f7464e0398a[/TD]
[TD]1/3/2018 12:00:00 AM[/TD]
[TD]True[/TD]
[TD]1/3/2018 4:15:16 PM[/TD]
[TD]DEF - 27[/TD]
[TD]ABD571[/TD]
[TD]HR[/TD]
[TD][/TD]
[TD][/TD]
[TD]LS[/TD]
[TD]Outbound[/TD]
[TD]Coaching,Referral Call[/TD]
[TD]Individual[/TD]
[TD]Telephonic[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Multiple[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

We could define a dynamic named range for the data you now have, which would capture new data: deleting old and pasting the new data starting in the same first cell. is this what you are after?

 

[Amosbroker]

The data will change in all cells, weekly - the headers will not change and the criteria I am looking for will not change - I am exporting call detail notes and reporting on unique members engaged and total sessions/calls. I have the total calls down, I just don't know how to report on unique members. So, for example -I export an excel sheet similar to the data above but drop it in to wordbook where it spits out engagement based on:
Column G = "" (blank)
Column O = Completed
Column L - Coaching
Column F contains my ID's I want unique total

 

[Amosbroker]

Aladin - I re-read your message and you are correct. I believe a dynamic named range would work? How would I incorporate that in to my existing formula? THANKS A TON!

 

[Aladin Akyurek]

Aladin - I re-read your message and you are correct. I believe a dynamic named range would work? How would I incorporate that in to my existing formula? THANKS A TON!

Care to post the formula as you have implemented it?

Also, what is the current range in which is the data is located and the name of the sheet housing the data?