SUM Unique values

I have an Excel 2007 spreadsheet that contains duplicates of the same work requests. I need to count the unique number of work requests based on other criteria but am struggling to do this.

For example:
a1 = 01/01/2011
a2=01/02/2011
c2=car
d2=wheel

=Sumproduct((Sheet1!b2:b5000>a1)*(Sheet1!b2:b5000<a2)*(Sheet1!c2:c5000=c2)*((Sheet1!d2:d5000=d2

Worksheet I am calculating data on has information such as:

Work Request      Date Raised      Vehicle      Part             Code
WR1365                           01/01/2011      truck      Exhaust       E1234
WR1365                           01/01/2011      truck      Exhaust       E1235
WR1365                           10/01/2011      truck      engine         EG1234
WR1231                             15/01/2011   bike               chain           C1234
WR137                                12/01/2011  car                wheel          W1233
WR137                                12/01/2011  car                wheel          W1235


I want the SUMPRODUCT formula to return that there is 1 Car Wheel query even though there are two rows on the worksheet. I cannot work out how to do this. I appreciate that this is a simple SQL query using MSAccess or the like but I am hoping to calculate this in MSEXCEL instead.
JayceWAsked:
Who is Participating?
 
JayceWAuthor Commented:
ModCorlEEone

Can I please cancel my initial "Close Question" request as I agree with Rorya that it is appropriate that the experts get some points assigned to them due to their efforts.

Thanks
0
 
Rory ArchibaldCommented:
Do you actually need to keep the duplicate rows, or could you remove them using the wizard first?
0
 
JayceWAuthor Commented:
Sadly I need to keep them and am trying to avoid using an advanced filter, copying unique data elsewhere.

What do you think?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jppintoCommented:
But if you have two rows, why do you want the formula to return 1?!?
0
 
Rory ArchibaldCommented:
You could add an additional key column (say col F) using a formula like (assuming your data in A:E):
=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2
and then another column (say G) use a formula like:
=1/COUNTIF($F$2:$F$1000,$F2)

then your counting formula becomes:

=Sumproduct((Sheet1!b2:b5000>a1)*(Sheet1!b2:b5000<a2)*(Sheet1!c2:c5000=c2)*(Sheet1!d2:d5000=d2)*Sheet1!G2:G5000)


0
 
JayceWAuthor Commented:
The data I have used as an example is not the actual data I am reporting on, but sadly I can't share that information. If you could see the actual spreadsheet it would be quite apparent why only the single count is required.
0
 
jppintoCommented:
Please take a look at the attached example.

You could go around this by doing something like this, no?

=IF(SUMPRODUCT((A1:A3="Test")*(B1:B3=14))>1,1,SUMPRODUCT((A1:A3="Test")*(B1:B3=14)))

jppinto
Book1.xlsx
0
 
KnutsonBMCommented:
are you ok with a VBA Solution?

knutsonbm
0
 
barry houdiniCommented:
This formula should give you a unique count in a single cell

=SUM(IF(FREQUENCY(IF((Sheet1!B2:B5000>A1)*(Sheet1!B2:B5000<A2)*(Sheet1!C2:C5000=C2)*(Sheet1!D2:D5000=D2),MATCH(Sheet1!A2:A5000,Sheet1!A2:A5000,0)),ROW(Sheet1!A2:A5000)-ROW(Sheet1!A2)+1),1))

It's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

....but that formula might be quite slow for 5000 rows of data - Rory's approach will be quicker

regards, barry
0
 
barry houdiniCommented:
Here's an example of that formula in action

In sheet1 only rows 2, 4 and 8 satisfy all the criteria.....but those rows all have "A" in column A so the formula (in sheet2 E2) returns 1

regards, barry
26916069.xlsx
0
 
JayceWAuthor Commented:
Thanks Rory but when I use the formula to add a flag column the column value is always 1. How does this help me.....or am I missing the obvious?
0
 
Rory ArchibaldCommented:
Sorry - I included the Code column in the key formula, which makes all rows unique I suspect. Change the formula in F to:
=A2&"|"&B2&"|"&C2&"|"&D2
0
 
JayceWAuthor Commented:
I tried removing one of the rows but no success.

I have tried this formula instead and it seems to work ok.
=(COUNTIF($A$2:A5000,A2)=1)+0
Do you see any issues with that instead?
0
 
Rory ArchibaldCommented:
It depends. Should the first three rows in your example be counted as 1 or 2?
0
 
Rory ArchibaldCommented:
Let me rephrase - if row 3 in your example was also exhaust rather than angine, and the dates match your criteria, should that be 1 or 2 counted?
0
 
Rory ArchibaldCommented:
Note: engine, rather than angine!
0
 
JayceWAuthor Commented:
I stand corrected my formula doesn't work.

In answer to your question, I would expect row 2 to return 1 and then rows 3 and 4 0. Row 5 1, Row 6 1 and Row 7 0.
0
 
Rory ArchibaldCommented:
I don't follow that based on your sample. In the attached, what would you expect the return value to be - 1 or 2 (or something else!)?
All three are the same for columns A, C and D, the dates are different but they all meet the criteria.
Count-unique-test.xls
0
 
JayceWAuthor Commented:
apologies if I have been unclear.

Basically for the mini example that you have copied. I want to count that there were 3 work requests raised in January, WR1365, WR1231 and WR137.  

0
 
Rory ArchibaldCommented:
That doesn't really tally with what I posted, but see if the attached does what you want. It uses two additional columns as before. Column F:
=IF(AND(B2>=$J$2,B2<=$J$3,C2=$J$4,D2=$J$5),A2,"")
where J2 and J3 are the start and end dates, J4 is the vehicle and J5 is the part.
Then col g:
=IF(F2<>"",1/COUNTIF(F:F,F2),0)

then the count is a straight sum of column G.

Count-unique-test.xls
0
 
martykCommented:
I am not a pivot table expert but I think you can do this with a pivot table.

Marty
0
 
Rory ArchibaldCommented:
I'd be very interested to see a pivot table solution that produces distinct counts without using formulas in the source data.
0
 
jppintoCommented:
Any feedback on my proposed solution?!
0
 
martykCommented:
Here is one solution with a pivot table.

EE-26916069-mk1.xls

The table is on the second sheet. I wasn't sure if a second sheet was excluded in the specification.

It uses a formula to count the number of text items in the first column which, in this case, is the number of unique work requests that match the criteria in columns B and C (vehicle and parts). The code and date raised columns are not included because they are not criterion.

Marty
0
 
Rory ArchibaldCommented:
But that returns 2, not 1, which is the desired count - since the WR number is the same.
0
 
martykCommented:
Yes you are right.

The count is not inside the pivot table. Instead the result is in 'pivot sheet' cell B2.  The formula for this cell was not robust because the table moves as columns are added or selected. It is better in this one.

 EE-26916069-mk2.xls
0
 
JayceWAuthor Commented:
Sorry for the slow repsonse guys;

jppinto I would be interested to see the VBA option that you are suggesting?

Rory you example counts 1 whereas I would have expected a count of 3 on the sample wokrsheet you posted. Those being WR1365, WR1231 & WR137.

It seems that this problem is more complex than I thought......I may need to employ something like Crystal Reports.




0
 
Rory ArchibaldCommented:
My example counts one because only WR137 matches the car and wheel criteria.
0
 
JayceWAuthor Commented:
Sorry Rory I see now. So for each metric that I widh to caluclate I would add a column containing an IF statement identifying the criteria I need?
0
 
Rory ArchibaldCommented:
It depends. If you want to use multiple combinations of vehicle and part for a given date range, then I would use one column to get distinct counts and then use a pivot table. See attached demo. You can alter the dates on the right then refresh the pivot table.
Count-unique-test.xls
0
 
jppintoCommented:
I'm not proposing any VBA solution...I proposed a formula like this:

=IF(SUMPRODUCT((A1:A3="Test")*(B1:B3=14))>1,1,SUMPRODUCT((A1:A3="Test")*(B1:B3=14)))

Please read my 2nd post from top where I've attached a sample file.

jppinto
0
 
Rory ArchibaldCommented:
It would be more useful if you posted what you used so that this would have some point as a PAQ and we wouldn't all have wasted our time. :)
0
 
JayceWAuthor Commented:
OK Rory fair point.

I changed my reporting strategy and simply moved away from Excel and used Crystal reports 8, enabling me to use Distinct counting features provided by such a SQL tool.
0
 
Rory ArchibaldCommented:
OK well that part doesn't really help as an Excel PAQ, but you did get several solutions that all work (even if you didn't use them), so I think you ought to be splitting points rather than deleting, unless you disagree?
0
 
JayceWAuthor Commented:
knutsonbm would you be good enough to upload your proposed VBA solution so that I can view it please?
0
 
JayceWAuthor Commented:
.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.