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

Sumproduct with Countif Returning Incorrect Unique Value

S

Smorris

New Member
Hello Chandoo! This is my first post, so please let me know if I have broken any etiquette rules. I have been trying for about three days now to get a unique count based on multiple criteria. Using some forum solutions I found online (Chandoo included), I have a formula that "mostly" works, but is not returning the correct count. I've uploaded an excerpt/sample sheet, but basically, here's what I need to do...

I have a list of tickets sold by customer ID numbers. Each row represents 1 ticket and includes the event code and the customer ID number. I am looking to get a unique count of event codes for each unique customer ID number. (I need a one cell solution that does not require filtering because it's a huge list and I eventually need the count on every row record for export to a different program.)

The closest I've gotten is (I've replaced the exact cell references with column headings to make it easier to read):

=SUMPRODUCT((CustomerID=B2)*(IFERROR(1/COUNTIFS(EventCode,EventCode,CustomerID,B2),0)))

I'm getting results that are really close. So, in the sample included, Customer ID "10" should result in 33 unique Event Codes. But the formula returns 32.5. Customer ID "23" correctly returns 1 unique Event Code. But then Customer ID "211" returns 1.5 instead of 2 and Customer ID "2939" returns 2 1/3 instead of 4. If it was always off by .5 I could simply adjust the formula (though I don't understand why), but this doesn't seem to be the case.

I freely admit that my understanding of this formula is rudimentary, so I think I'm missing something basic, but I don't have a lot of experienced Excel formula users here at work to bounce things off of. Any help would be GREATLY appreciated!
 

Attachments

Luke - Wow! What a fast response - thank you so much! Took a look and Vijay's formula works like a charm! (I think it might take the rest of 2014 for me to understand it, but that's a far, far better problem to have than no solution at all!)
 
Hi Morris ,

I am not being abrasive , but this is something I have ranted on in this forum for quite some time.

Those who approach this , as well as other forums , invariably ask for single-cell formulae , when it is beyond my comprehension as to how , given all that we know of Excel's recalculation mechanism , a single-cell formula , which needs to be copied down thousands of rows , can ever be faster than using helper columns.

I have highlighted a portion of my earlier sentence , because this is the key ; if you want a single complex formula , which can do everything within itself , and spit out one answer , which is all that you want , then certainly it is logical to think that this one formula will be fast , compared to using helper columns and putting in thousands of simpler formulae.

However , when you are going to copy the same complex formula down thousands of rows , it is anyone's guess which approach will be faster. You need to try both to find out.

My approach is that by using simpler functions which you know , and by trying to develop a problem solving attitude , you will be moving away from Excel formulae , and improving your problem solving skills. I am not against complex formulae ; certainly a mastery of Excel , in part , involves a mastery of all the available functions , or at least as many as are necessary for your purposes. But to think that the ability to create formulae which can run to a paragraph in one of Charles Dickens' novels is necessary to use Excel masterfully , what can I say.

Having said that , can you check out the approach in the uploaded file , and see whether it gives you the correct results in your working file ?

The logic itself is very simple , and understanding it should not take anyone more than a few minutes. Of course it may be wrong , but given that it is simple to create and understand , it may also be simple to correct.

Narayan
 

Attachments

Narayan and Somendra,

Thank you so much for these alternative solutions - I hope one day I can be competent enough in Excel to pay this help forward.

Somendra - I have no experience with pivot tables, so I will definitely take a look at this link this afternoon and start familiarizing myself with this option. (I also JUST (as in two days ago) upgraded to Excel for Mac 2011 from 2008, so I am excited to see what this magical VBA is.)

Narayan - A couple of days ago I was trying to create a multiple column solution, but I was not creative enough to get there on my own apparently (insert "Derp" sound). Your solution is quite elegant (I didn't know I could combine two cells like that!) and I will spend some more time walking through it to make sure I understand each step. And I promise in future not to neglect a series of simple functions in lieu of a massive one :)
 
You must log in or register to reply here.
Back
Top