So I'm trying to get a formula that counts only unique values based on 2 columns. If you see the attached image as an example, I want the formula to tell me that Order 55370 counts as having Base# KH-1525HR only 1. Though that part has several line items on that order (Job#s) I only want it to tell me that this individual order ordered that base # once. Does this make sense?
Capture.JPG
First, you need to have a column of non-unique values for the JobNo 55370-01 and 55370-02 are seen as unique by Excel. So split that column into two, or use a helper column with the first 5 digits only.
Then, array-enter : enter using Ctrl-Shift-Enter a formula like
=SUM(1/COUNTIFS(C2:C10,C2:C10,D2:D10,D2:D10))
Where C and D have the Job No (without -01, -02) and Base#.
This formula does not handle blanks well, so make sure that you only reference filled cells.
Bernie Deitrick
Excel MVP 2000-2010
Fantastic!
The Array portion is what I wasn't getting on my own. Works great.
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks