Multiple criteria COUNTIFS with distinct values

[tonyhill65]

Using excel 365
I have an enquiries received database with multiple columns of information
I have an existing formula that counts how many quotes an estimator has issued in a month
=COUNTIFS($AF:$AF,">="&M$4,$AF:$AF,"<="&M$5,$AN:$AN,$B46)
where $AF is the date sent column, M$4 is for example 1st April and M$5 is 30th April, $AN holds the estimator's name and $B46 also holds the estimator's name - we use this as a compare to or filter.

this all works fine except when we send the same quote to multiple customers. For us thats 1 quote issued. However if we were to send the same quote to 3 customers the formula returns 3 and not 1.
So we need to add in to return only distinct values (I think thats the right term)

We have a quote ID column that has a quote number in. Starts at 1 and increases for each quote we issue. When we issue the same quote to multiple customers each row has the same quote ID number. This column is $AC

I've been trying with UNIQUE(array), but can't get that to work as COUNTIFS requires a criteria after the range, Also have been trying to include 1/COUNTIFS(array etc., but also get errors with that.

I'd appreciate any help you can offer.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(AC:AC,($AF:$AF>=M$4)*($AF:$AF<=M$5)*($AN:$AN=$B46))))

Although it's best to limit the rows, rather than using entire columns.

 

[Phuoc]

...

 

[tonyhill65]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(AC:AC,($AF:$AF>=M$4)*($AF:$AF<=M$5)*($AN:$AN=$B46))))

Although it's best to limit the rows, rather than using entire columns.

Oh, Wow!, Fluff that works like a dream

I added row numbers as you suggested and quickly tested on each month. Boom!

thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.