# Frequency Formula - Meeting multiple criteria

#### Amosbroker

##### New Member
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

### Excel Facts

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

#### Amosbroker

##### New Member
Someone - Hellllllllp :/

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

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

M.

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

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

##### MrExcel MVP
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
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
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!

##### MrExcel MVP
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?