# How to countif when there are duplicate values [SOLVED]

#### exceljockey

##### Member
HI

I am currently working with a database which has a bunch of different columns: http://i.imgur.com/WXiV1D1.png

Now I need to count how many jobs were received, under two conditions: Month (col 6) & Site_Code (col 3).

I did a simple countif to count how many jobs were listed under these two conditions, however, each job is split up into multiple rows under one certain Order ID (col A). So my current forumlas pick up too many jobs as its just counting how many rows under the two conditions, and isn't adding the multiple rows under the one order ID.

How would I make it so that it picks up only unique order IDs under the two conditions previously stated?

E.G. Rows 22797 - 22801 is all one Job ID but the countif formula would list it as 5 different jobs (obviously).

I Have looked into countif for uniques, but I really don't understand how this works, so would like somebody to elaborate!

Thanks

EJ

#### shrivallabha

##### Excel Ninja
EJ,

You can try pivot table, if you have not. In pivot table you can manipulate data in many ways.

So you can have a Pivot which lists:

Month (e.g.JULY) | JOBS in Month (e.g.JULY) | Count of Jobs

If there's a need for formula based solution then let us know.

#### exceljockey

##### Member
Hi

I need a formula solution since its needed for my dashboard; otherwise I would have set up a pivot table as you suggested!

#### SirJB7

##### Excel Rōnin
Hi, exceljockey!

Tried using COUNTIFS function instead of COUNTIF?

Usage: =COUNTIFS(range1,criteria1[,range2,criteria2...])

So you could specify different conditions to fit your case and count unique combinations.

Regards!

#### exceljockey

##### Member
Hi

I was previously using Countifs!

This doesn't solve my problem however, since multiple rows count as one job. I need to differentiate between unique Order IDs, so essentially I need a FREQUENCY function inside countifs somehow?? That's what im guessing anyway.

Thanks

#### SirJB7

##### Excel Rōnin
Hi, exceljockey!

Try this formula adjusting range as required:

=SUMA(SI(FRECUENCIA(A1:A5;A1:A5)&#62;0;1)) -----&#62; in english: =SUM(IF(FREQUENCY(A1:A5,A1:A5)&#62;0,1))

Regards!

PS: Source: built-in FREQUENCY function help. Just as a tip, remember that if you enter in an empty cell "=FREQUENCY (" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

#### exceljockey

##### Member
Hi SirJB7

Thanks for the help, this works but only shows how many unique values there were, it doesn't show how many unique values there were UNDER GIVEN CONDITIONS, which is more what I need.

Please let me know if you know how to fix this!

Thanks

EJ

#### NARAYANK991

##### Excel Ninja
Hi ,

Have you tried something like this ?

=SUM(IF((Table1[Site_Code]=C2)*(Table1[Month]=F2),1/COUNTIFS(Table1[Site_Code],C2,Table1[Month],F2,Table1[Order_ID],Table1[Order_ID])))

This is to be entered as an array formula , using CTRL SHIFT ENTER.

Narayan

#### exceljockey

##### Member
I have found a fix by using a helper column Nice and simple. Will look into what you replied with anyway for the future!