• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to countif when there are duplicate values [SOLVED]

E

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
 
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.
 
Hi


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


Thanks in advance
 
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!
 
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
 
Hi, exceljockey!


Try this formula adjusting range as required:

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


Just advise if any issue.


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.
 
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
 
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
 
You must log in or register to reply here.
Back
Top