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.
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Do you actually need to keep the duplicate rows, or could you remove them using the wizard first?
0
Author 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
Commented:
But if you have two rows, why do you want the formula to return 1?!?
0
Commented:
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)

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

0
Author 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
Commented:
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
Commented:
are you ok with a VBA Solution?

knutsonbm
0
Commented:
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
Commented:
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
Author 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
Commented:
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
Author 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
Commented:
It depends. Should the first three rows in your example be counted as 1 or 2?
0
Commented:
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
Commented:
Note: engine, rather than angine!
0
Author 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
Commented:
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
Author 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
Commented:
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
Commented:
I am not a pivot table expert but I think you can do this with a pivot table.

Marty
0
Commented:
I'd be very interested to see a pivot table solution that produces distinct counts without using formulas in the source data.
0
Commented:
Any feedback on my proposed solution?!
0
Commented:
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
Commented:
But that returns 2, not 1, which is the desired count - since the WR number is the same.
0
Commented:
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
Author 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
Commented:
My example counts one because only WR137 matches the car and wheel criteria.
0
Author 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
Commented:
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
Commented:
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
Commented:
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
Author 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
Commented:
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
Author 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

Experts Exchange Solution brought to you by