Frequency Formula - Meeting multiple criteria

Amosbroker

New Member
Joined
Mar 26, 2018
Messages
31
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

New Member
Joined
Mar 26, 2018
Messages
31
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

New Member
Joined
Mar 26, 2018
Messages
31
HSDateIsCompleteLastUpdateDTmGroupUserNameRiskStrat1bsuccessdetails_Question1dremoved_Question1WhoCalled_Question3ContactType_Question2SessionType_Question4Audience_Question5ContactMethod_Question6SessionOutcome_Question8messageleft_Question9additionalattempts_Questiona1successstory_Questionb1TopicsDiscussed_Question
5c47f748-48a7-4420-8521-a018b7617bee1/1/2018 12:00:00 AMTrue1/2/2018 5:10:32 PMABC-11ABD568No detailsLSOutboundCoachingIndividualTelephonicCompletedYesMultiple
5c47f748-48a7-4420-8521-a018b7617bee1/2/2018 12:00:00 AMTrue1/2/2018 4:06:35 PMABC-12ABD569JBInboundCoachingIndividualTelephonicCompletedNoMultiple
60864b47-f8a5-4fe0-a7fe-afa154a4bb7a1/2/2018 12:00:00 AMTrue1/2/2018 8:24:05 PMABC-13ABD570HRJBInboundCoachingIndividualTelephonicCompletedMultiple
5173db51-2e31-46b5-888a-03c15a9778921/3/2018 12:00:00 AMTrue1/3/2018 8:18:56 PMDEF - 23ABD5713 days freeJBOutboundCoachingIndividualTelephonicCompletedYesMultiple
fcc91ce3-0831-47fd-9c67-2fa29611d9f51/3/2018 12:00:00 AMTrue1/3/2018 7:53:20 PMDEF - 24ABD568JBInboundCoachingIndividualTelephonicCompletedNoMultiple
a4dd70dd-229d-4aae-ae0f-422f087227071/3/2018 12:00:00 AMTrue1/3/2018 9:41:40 PMDEF - 25ABD568No progressJBInboundIndividualTelephonicCompletedYesMultiple
d41159d5-0fd3-4607-ac78-4b10536fc0491/3/2018 12:00:00 AMTrue1/3/2018 9:25:42 PMDEF - 26ABD569LSInboundIndividualTelephonicCompletedNoMultiple
3488c145-34d1-492a-890b-7f7464e0398a1/3/2018 12:00:00 AMTrue1/3/2018 4:15:16 PMDEF - 27ABD571HRLSOutboundCoaching,Referral CallIndividualTelephonicCompletedNoMultiple

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

New Member
Joined
Mar 26, 2018
Messages
31
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

New Member
Joined
Mar 26, 2018
Messages
31
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,380
Messages
5,444,119
Members
405,268
Latest member
JLEMS

This Week's Hot Topics

Top