This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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.

For example:

a1 = 01/01/2011

a2=01/02/2011

c2=car

d2=wheel

=Sumproduct((Sheet1!b2:b50

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

What do you think?

=A2&"|"&B2&"|"&C2&"|"&D2&"

and then another column (say G) use a formula like:

=1/COUNTIF($F$2:$F$1000,$F

then your counting formula becomes:

=Sumproduct((Sheet1!b2:b50

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

=IF(SUMPRODUCT((A1:A3="Tes

jppinto

Book1.xlsx

=SUM(IF(FREQUENCY(IF((Shee

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

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

=A2&"|"&B2&"|"&C2&"|"&D2

I have tried this formula instead and it seems to work ok.

=(COUNTIF($A$2:A5000,A2)=1

Do you see any issues with that instead?

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.

All three are the same for columns A, C and D, the dates are different

Count-unique-test.xls

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.

=IF(AND(B2>=$J$2,B2<=$J$3,

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,F

then the count is a straight sum of column G.

Count-unique-test.xls

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

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

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.

Count-unique-test.xls

=IF(SUMPRODUCT((A1:A3="Tes

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

jppinto

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.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.