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]

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>

 

[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?