adding a Frequency function to a CountIFS function

**Plucky_** · 03-10-2017, 01:16 PM

Hi

Is it possible to include a frequency function to a COUNTIFS function?

Im trying to count how many deliveries a driver has made in a day by using a sheet that contains multiple jobs with information such as site delivered to.
The problem I have is my COUNTIFS is counting all jobs that have been delivered by a driver on a particular day even if they have all gone to the same customers address.
I need it to include a frequency function on column D to just count the individual customers sites he delivers too.

=COUNTIFS(ACOL_All!F:F,71003,ACOL_All!J:J,$B6)

F column is the drivers ID "71003"
J column is the Delivery Date ( B6 is the Date )
D column is the Customers ID

any help would be much appreciated.

**mcmahobt** · 03-10-2017, 04:08 PM

Can you upload a sample workbook?

**Plucky_** · 03-10-2017, 04:27 PM

Hi mcmahobt

Ive entered the values I need manually in red on Sheet2.

**FlameRetired** · 03-10-2017, 05:35 PM

Edited Formula.

Hi Plucky_. Welcome to the forum.

I don't think COUNTIFS is the function of choice here.

With the understanding that 'Item No' is in cell D1 and is referenced rather than hard coded into the formula:

Try this array entered formula and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=COUNT(1/IF(FREQUENCY(IF(($D$1=ACOL_All!$F$2:$F$1005)*(B6=ACOL_All!$H$2:$H$1005),ACOL_All!$D$2:$D$1005),ACOL_All!$D$2:$D$1005),ACOL_All!$D$2:$D$1005))

**FlameRetired** · 03-10-2017, 07:25 PM

The array formula can be simpler still

Formula:

=COUNT(1/FREQUENCY(IF(($D$1=ACOL_All!$F$2:$F$1005)*(B6=ACOL_All!$H$2:$H$1005),ACOL_All!$D$2:$D$1005),ACOL_All!$D$2:$D$1005))

**Plucky_** · 03-11-2017, 12:26 AM

Brilliant, Thats perfect.

Thanks for your help.

**FlameRetired** · 03-12-2017, 08:13 PM

You're welcome. Glad to help, and thanks for the feedback.

adding a Frequency function to a CountIFS function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Re: adding a Frequency function to a CountIFS function

Thread Information

Users Browsing this Thread

Similar Threads

COUNTIFS function makes excel slow, how to make the function "shorter"

COUNTIF/COUNTIFS function + nested user-defined function

Use COUNTIFS function on results of DATEVALUE function?

[SOLVED] Frequency Function

Excel 2007 : help with a FREQUENCY function

Frequency Function

[SOLVED] Frequency function

Bookmarks

Bookmarks

Posting Permissions