• 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 Count Unique Values using multiple criteria

G

Gary Furnival

New Member
Hi,

I want to count the number of unique values (i.e the number of individuals in column I of attached file) in a particular cost centre (column D of attached file) where the date (column G) is between two dates. Having searched the internet for answers I have arrived at the formula shown in cell L12 but it is not calculating properly. Can anyone help please.

Regards, Gary
 

Attachments

There are issues with your cell formatting for your numbers... They are not numbers, and col K formatting was off for 2 cells as well... I converted col D to numbers and K to numbers and used a sumproduct function to get you desired results...

=SUMPRODUCT(($G$2:$G$13062<=L$3)*($G$2:$G$13062>=L$2)*(D2:D13062=$K6))

Respectfully,
PaulF
 

Attachments

PaulF said:
There are issues with your cell formatting for your numbers... They are not numbers, and col K formatting was off for 2 cells as well... I converted col D to numbers and K to numbers and used a sumproduct function to get you desired results...

=SUMPRODUCT(($G$2:$G$13062<=L$3)*($G$2:$G$13062>=L$2)*(D2:D13062=$K6))

Respectfully,
PaulF
Click to expand...
Paul,

Thank you for the reply. I could get the same result but what I want to count is the number of unique people who have a record (row) for a particular cost code within the date range; e.g where the sumproduct calculation for 310010104 in Apr-14 is 261 records I want to count the fact that these are for 12 unique people. Is there a way to get to this extra step.

Regards, Gary
 
Hi Gary,

Just do double-check, was there a problem with the solution I posted? Your initial response indicated that it was good, but I see the thread is continuing.
 
Luke M said:
Hi Gary,

Just do double-check, was there a problem with the solution I posted? Your initial response indicated that it was good, but I see the thread is continuing.
Click to expand...
Hi Luke,

PaulF also responded but his response did not provide a complete solution hence my reply to him.

Regards, Gary
 
Luke,

There are many way to solve most items in Excel as we all know and I'm just trying to learn and work on most of the items in here...

I did not understand that if a solution is presented that we can't provide additional thoughts like I've seen on many other posts...

I'm sorry if I offended you...

Respectfully,
PaulF
 
Hi PaulF,
No offense taken! :) I was just checking to make sure OP had received a valid answer. My apologies if my response gave off the wrong impression.

Gary,
Completely understand, appreciate the feedback as I'm sure PaulF does as well.
 
You must log in or register to reply here.
Back
Top