Count multiple criteria including date range and unique contact names

[Spambalam]

Hello Excel Wizards! I’m hoping someone can help me as I’m going out of my mind.

I’m creating a stats sheet to record contact types made throughout the year, broken down into a monthly view. I’m currently trying to establish a formula to tell me the number of phone calls made in a month to a unique person (I.e. if they’ve had multiple calls in July, then they’re only counted once)



Cell references:-

Contact date: July!A:A

Contact name: July!B:B

Contact type: July!D:D



At present, my formula counts unique contacts within the stipulated dates for July, I just can’t work out how I add that I also need it to consider the contact type made.



Formula I’ve currently got:

=SUMPRODUCT(IF((July!$A$2:$A$2499<=DATE(2022,7,31))*( July!$A$2:$A$2499>=DATE(2022,7,1)), 1/COUNTIFS(July!$A$2:$A$2499,"<="&DATE(2022,7,31), July!$A$2:$A$2499, ">="&DATE(2022,7,1), July!$B$2:$B$2499,July!$B$2:$B$2499), 0))


As much as I would love to use a pivot table for this, it's not an option.

Any help where to add the stipulation of contact type to this would be greatly appreciated!

[Richard Buttrey]

Have you considered the COUNTIFS() function which largely replaced the popular SUMPRODUCT option when XL 2007 was introduced.

[WideBoyDixon]

If you're on O365 then you should be able to leverage the UNIQUE/FILTER/LET functions. Something like:

Please Login or Register  to view this content.

That will return the list of unique names/types. Or you could use:

Please Login or Register  to view this content.

To return the count

WBD

[nick.williams]

How about:

=LET(dates,July!A:A,names,July!B:B,types,July!D:D,IFERROR(ROWS(UNIQUE(FILTER(names,(dates>=DATE(2022,7,1))*(dates<=DATE(2022,7,31))*(types=1)))),0))

I used a generic type of 1, in red above, which you will need to change to suit.

If you will always want a whole month then you could shorten it to:

=LET(dates,July!A:A,names,July!B:B,types,July!D:D,IFERROR(ROWS(UNIQUE(FILTER(names,(MONTH(dates)=7)*(types=1)))),0))

[alansidman]

You could use the Group By function of Power Query to count the number of times for the specific criteria. If you are interested in learning this, then post a sample of your data but do not post a picture.

Please read the yellow banner at the top of this page on how to attach a file.