Using sum if frequency to count unique values based on certain criteria

Hi All,

I am having a problem with the below formula I have entered in Sheet 2 of my workbook.
Where "D6 = Agent Name" "D8 = Criteria 1" "D9 = Criteria 2" "D10 = Criteria 3" "D11 = Criteria 4"

=SUM(IF(FREQUENCY(IF('Sheet 1'!$C:$C<>"""",IF('Sheet 1'!$G:$G=D8,IF('Sheet 1'!$H:$H=D6,MATCH(""~""&'Sheet 1'!$C:$C,'Sheet 1'!$C:$C&"""",0)))),ROW('Sheet 1'!$C:$C)-ROW('Sheet 1'!$C$2)+1),1))+SUM(IF(FREQUENCY(IF('Sheet 1'!$C:$C<>"""",IF('Sheet 1'!$G:$G=D9,IF('Sheet 1'!$H:$H=D6,MATCH(""~""&'Sheet 1'!$C:$C,'Sheet 1'!$C:$C&"""",0)))),ROW('Sheet 1'!$C:$C)-ROW('Sheet 1'!$C$2)+1),1))+SUM(IF(FREQUENCY(IF('Sheet 1'!$C:$C<>"""",IF('Sheet 1'!$G:$G=D10,IF('Sheet 1'!$H:$H=D6,MATCH(""~""&'Sheet 1'!$C:$C,'Sheet 1'!$C:$C&"""",0)))),ROW('Sheet 1'!$C:$C)-ROW('Sheet 1'!$C$2)+1),1))+SUM(IF(FREQUENCY(IF('Sheet 1'!$C:$C<>"""",IF('Sheet 1'!$G:$G=D11,IF('Sheet 1'!$H:$H=D6,MATCH(""~""&'Sheet 1'!$C:$C,'Sheet 1'!$C:$C&"""",0)))),ROW('Sheet 1'!$C:$C)-ROW('Sheet 1'!$C$2)+1),1))

The data this looks into in Sheet 1 is as follows...

Column C Column D Column E Column F Column G Column H Column I
Company 1 Initials Phone Call Agent Name Date + Time
Company 2 Initials Phone Call Agent Name Date + Time
Company 3 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 4 Initials Phone Call Agent Name Date + Time
Company 5 Initials Phone Call 2 Agent Name Date + Time
Company 6 Initials Phone Call Agent Name Date + Time
Compnay 7 Initials Phone Call 2 Agent Name Date + Time
Compnay 7 Initials Phone Call 2 Agent Name Date + Time

Column C - Company
Column D - Initials
Column E and F are blank
Column G - Phone Call Type
Column H - Agent Name
Column I - Date and Time Stamp

I am using the formula to calculate how many unique companies (Column C) have been contacted by agent (Column H). This also needs to be calculated against the criteria in Column G. There are hundreds of records and they are all mixed in together. Column G can have many different fields so I only want to pick out the phone calls which are labelled Phone Call, Phone Call 2, Phone Call 3, and Phone Call 4. Can this be simplified into "Phone Call*" possibly? Based on the above data the answer is 7 after pulling this data out in a separate sheet the following formula tells me 7 records but has no criteria to reference =SUM(IF(FREQUENCY(MATCH(C2:C15,C2:C15,0),MATCH(C2:C15,C2:C15,0))>0,1)) the above formula I am needing help with returns 13?

Please can anyone make sense of what I am saying and help me out please!

Sorry, posted in incorrect thread.
please ignore my reply.

Hi and welcome to the forum.

First off, I think you should know that the formula you give has to be probably the most resource-heavy single formula I think I have ever seen.

That formula is being forced to calculate, at a rough estimate, nearly 21 million cells before arriving at a result. That's an absolutely incredible amount for a single formula.

The overriding reason is your use of entire column references. Do you really have data extending all the way down to row one million-plus?

If not, I strongly recommend that you either choose a suitably low, though sufficient, upper bound for the end row being referenced or, even better, make your ranges dynamic, such that they automatically adjust as your data expands or contracts.

The point is that array formulas calculate over all cells passed to them. And that applies whether those additional cells are technically beyond the last-used cells in those ranges or not.

With some functions, e.g. COUNTIF(S)/SUMIF(S), you can get away with referencing entire columns with no detriment to performance, though not with functions which operate over arrays, such as AGGREGATE, SUMPRODUCT, and any construction requiring CSE.

Regards

Thanks.

Where are your results supposed to go? Have you set-up a table ready to house them? If not, could you do so and re-attach your workbook?

Regards

So you're just looking for formulas to replace your existing ones next to all the Unique Contacts entries? That is, improved, more efficient versions, but the results you're currently getting are nevertheless correct?

Regards

So for cell E23 you should be using:

=SUM(IF(FREQUENCY(IF('Sheet 1'!$H$2:H200=$D$16,IF(ISNUMBER(MATCH('Sheet 1'!$G2:$G200,$D$17:$D$21,0)),MATCH("~"&'Sheet 1'!$C2:$C200,'Sheet 1'!$C2:$C200&"",0))),ROW('Sheet 1'!$C2:$C200)-ROW('Sheet 1'!$C$2)+1),1))

Note the upper row reference!

Regards

I would like the result to include the start date and end date if that makes a difference to the formula needed, thank you in advance.