SUMPRODUCT filtered

Hi All

I'm trying to do a SUMPRODUCT on a filtered range. The filters are on a date range in column B example 19/07/10 - 15/08/10 and where Column U = 1. Basically I want to count the number of times a certain category appears within this date range and where Column U = 1...the categories reside in column P and an example of some are "Powerpact", "Coupler" etc.

I've tried this but it doesn't work

=SUMPRODUCT((B3:B5231>19/7/2010)*(B3:B5231<=15/8/2010)*(P2:P5231="Powerpack")*(U2:U5231 = 1)))

Any help on this would be much appreciated

Regards

Donal

Originally Posted by daddylonglegs

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Hi There

Apologies for cross posting...I didn't read the rules properly and therfore didn't realise I was doing anthing wrong. I will not cross post in the future but if I do then I will provide the link from the other forum

Apologies again

Donal

Originally Posted by NBVC

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B5231,ROW(B3:B5231)-MIN(ROW(B3:B5231)),,1)),(B3:B5231>"19/7/2010"+0)*(B3:B5231<="15/8/2010"+0)*(P3:P5231="Powerpack")*(U3:U5231=1))

Note: range sizes must all be the same

A more universal approach to comparing date is to use the DATE() function... because the way your date is written wouldn't work in North America, for example.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B5231,ROW(B3:B5231)-MIN(ROW(B3:B5231)),,1)),(B3:B5231>DATE(2010,7,19))*(B3:B5231<=DATE(2010,8,15))*(P3:P5231="Powerpack")*(U3:U5231=1))

Hi there

Yes that solution works ...Thank you so much for your help its much appreciated and sorry for the cross posting

Regards
Donal