Count of unique contractors that were paid during the month

**DD1** · 01-19-2015, 09:00 PM

Hi - I would like to count the number of contractors that we paid during the month.

The problem I have is that a contractor can be listed several times depending on the type of service – e.g. standard hours, oncall, call-back etc.

I want to only count the contractor once if the sum of their payments is greater than zero and I want to do it with a formula rather than a pivot table.

Columns A to D in the attached file contains the source data. I have created a pivot table only to illustrate that I want to identify the number of contractors that the pivot table displays. Cell H20 shows the result is 16. I would like to enter a formula in cell H21 which references the source data.

I have done many searches but can’t find the solution to my specific scenario.

Can anyone help please?

Many thanks
David

**pickslides** · 01-19-2015, 09:45 PM

Here's my go at it. Not one formula but a nice summary..

**DD1** · 01-19-2015, 09:59 PM

Thanks a lot Pickslides. I appreciate your suggestion. Because of the way I have set up the file, I particularly want a single formula that I can copy across from month to month. I know you can do it with an array formula but I can't for the life of me work out how to do it.
Thanks
David

**bebo021999** · 01-19-2015, 10:06 PM

Is it counting unique distintc list with criteria in other column?

Try with one formula only:

=SUM(IFERROR(1/IF(SUMIF(A2:A67,A2:A67,D2:D67)>0,COUNTIF(A2:A67,A2:A67),0),0))

Or

=SUM(IFERROR(1/((SUMIF(A2:A67,A2:A67,D2:D67)>0)*COUNTIF(A2:A67,A2:A67)),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**DD1** · 01-19-2015, 10:11 PM

Wow that is exactly what I am after. That's excellent. Thanks for doing this. That's a big help.

Regards,
David

Count of unique contractors that were paid during the month

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count of unique contractors that were paid during the month

Re: Count of unique contractors that were paid during the month

Re: Count of unique contractors that were paid during the month

Re: Count of unique contractors that were paid during the month

Re: Count of unique contractors that were paid during the month

Thread Information

Users Browsing this Thread

Similar Threads

unique count the number of users in month regardless their frequency

Count number of unique cells if month =

[SOLVED] Formula to Count Unique Customer by Month

Count unique for specific month

Count unique days within month

Bookmarks

Bookmarks

Posting Permissions