PDA

View Full Version : SUMPRODUCT issues



Simon Lloyd
2011-10-21, 04:59 PM
Hi all, i'm trying to sum all instances of a certain acronym in a range provided that the acronym falls within a date range that i set using data validation dropdowns, the date provided by them is in this format 01/01/2011, the date in the data sheet that i'm summing from is in the same format. I'm having trouble getting SUMPRODUCT to work with these criteria
SUMPRODUCT(('Shift 1 2011'!$I$15:$K$379="TRN")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)+('Shift 1 2011'!$I$15:$K$379="SIC")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)+('Shift 1 2011'!$I$15:$K$379="LD")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)+('Shift 1 2011'!$I$15:$K$379="SAF")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)+('Shift 1 2011'!$I$15:$K$379="UN")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)+('Shift 1 2011'!$I$15:$K$379="SWP")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13))A11 and A13 are on my Summary sheet and hold the dates from the data validation, i'm sure it's something i've missed, i've been up 13 hours already so a little bleary eyed - all help appreciated :)

Ken Puls
2011-10-21, 06:36 PM
Holy smokes... I go bleary eyed just looking at that, and it's 9:30AM!

I think I'd want some sample data to test with, Simon... or better yet I'd want Bob, as he could probably knock that off in about 2 seconds... ;)

Simon Lloyd
2011-10-21, 06:45 PM
To be honest Ken it's a really repititious (is that how you spell it?) formula, it can be sliced like this =SUMPRODUCT(('Shift 1 2011'!$I$15:$K$379="TRN")--('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13)) what's ahppening is it doesn't seem to be bound by the dates in $A$11 and $A$13 so it will return a result if "TRN" appears anywhere in the 300 or so rows, but in reality i'm simply trying to confine it to this:
IF cell HAS "TRN" and A(CELL ROW) date IS >= $A$11 or date is <= $A$13 then COUNT, seems simple huh? i't's been driving me nuts, the formula i posted is only about 2/3's of it, i cut it down because its just the same over and over :)

Now that i've cleared the muddy waters can you see where i've gone wrong?

I've not seen Bob online today so maybe he's working away or resting for the weekend (that is if he knows how to take a rest ;))

Ken Puls
2011-10-21, 06:58 PM
Curious... what are your dates in A11 and A13?

Reason I'm asking... should "IS >= $A$11 or date is <= $A$13 " be "IS >= $A$11 AND date is <= $A$13 "

Simon Lloyd
2011-10-21, 07:06 PM
The dates are in two data validation cells on the same sheet as the formula, basically it's giving a snapshot view between a sunday and the following saturday so for january it could bo 01/01/2011 and in A13 08/01/2011, so on the sheet that has the acronyms "TRN" if the columns I:K have "TRN" within those dates found in column A of the same sheet ("Shift 1 2011") then add it to the count, so i need to do this with a dozen acronyms for the same range which is why the forumula is long and i have 54 ranges.

I could whizz it off easily with VBA but this workbook is going to be idiot proof only right now im the idiot :)

Simon Lloyd
2011-10-21, 07:13 PM
here's an example file for you to mess with :)

I've messed with forumla a few different ways, with double unary and without...etc

Ken Puls
2011-10-21, 07:30 PM
...for january it could bo 01/01/2011 and in A13 08/01/2011

I realize that, but let's look at 12/31/2003. It's not greater than 01/01/2011, but it is less that 08/01/2011. So to me, you'll end up catching every date, not matter what it is if you're truly using an OR syntax. If you're using AND, then you're restricting the range. Follow me?

Sorry Simon, crazy busy and don't have time to look at your sheet just now, but I'll try and loop back to it in a bit.

Simon Lloyd
2011-10-21, 07:41 PM
Hi ken i believe im using the AND operator *, but i see where you're coming from (even with your US date format :))

Simon Lloyd
2011-10-22, 08:13 PM
Hi Ken i've sorted this to a fashion, i used a named range for the date lookup area, i really don't understand why i couldn't use the actual range???

Here's what worked (using the double unary for coercion and the two AND operators although i would have expected the first * to really have been --):
=SUMPRODUCT(--('Shift 1 2011'!$I$15:$K$379="TRN")*(dList>=$A$11)*(dList<=$A$13))

Here's what didn't work:
=SUMPRODUCT(--('Shift 1 2011'!$I$15:$K$379="TRN")*('Shift 1 2011'!$A$15:$A$379>=$A$11)*('Shift 1 2011'!$A$15:$A$379<=$A$13))

They are for every purpose exactly the same but for some reason SUMPRODUCT doesn't like the use of the full range! (can't wait until Bob see's this).

JoePublic
2011-10-24, 02:43 PM
Simon,
Your dates are in column C not column A, so:
=SUMPRODUCT(('Shift 1 2011'!$I$15:$K$379="TRN")*('Shift 1 2011'!$C$15:$C$379>=$A$11)*('Shift 1 2011'!$C$15:$C$379<=$A$13))
works. There is no need for the double unary at the start - the coercion is handled by the multiplication anyway.

Simon Lloyd
2011-10-24, 05:23 PM
Joe thanks, i had noticed, i was working with another test page that i was working the formula on,when i went on to use named ranges i found my faux pas!, thanks for looking at it and providing a solution, i was at the end of a 14hr workday and a bit screen blind :)

JoePublic
2011-10-24, 05:37 PM
No worries - been there! ;)

Ken Puls
2011-10-24, 06:20 PM
... i found my faux pas!, thanks for looking at it and providing a solution, i was at the end of a 14hr workday and a bit screen blind :)

Hey Simon,

Sorry I couldn't help with this. I had a few minutes to look, but got lost in your sheet then called away for family stuff. Just getting back now!

Simon Lloyd
2011-10-24, 06:50 PM
Dont worry, guess we've all had a pressing week :), i'm off for 6 days now so a couple of days golf and a few drunk ;)

Ken Puls
2011-10-24, 07:15 PM
Week? Hah! More like year! LOL!

Enjoy the golf, Simon, and have a couple of scotch's for me. :)

Simon Lloyd
2011-10-24, 07:32 PM
Scotch isn't my thing, i like a nice strong lager to get merry with and then very long Bombay Saphire gin & tonics twist of lemon, twist of lime and lots of ice :).........can almost feel that glass now!

Simon Lloyd
2011-10-31, 08:39 PM
Well i've had to revisit this thread, i'm trying to use the indirect function to pick up the sheet name from a cell but i'm buggered if i can get it to work!
=S3-SUMPRODUCT((INDIRECT("'"&B3&"'!$H$15:$J$379="TRN"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="SIC"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="LD"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="SAF"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="UN"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="SWP"))*(dlist>=$A$11)*(dlist<=$A$13))Any help appreciated

JoePublic
2011-10-31, 11:25 PM
Parens in the wrong places:

=S3-SUMPRODUCT((INDIRECT("'"&B3&"'!$H$15:$J$379")="TRN")*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379")="SIC")*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379")="LD")*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379"="SAF")*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379="UN"))*(dlist>=$A$11)*(dlist<=$A$13)+(INDIRECT("'"&B3&"'!$H$15:$J$379"="SWP")*(dlist>=$A$11)*(dlist<=$A$13))

You can also simplify to:
=S3-SUMPRODUCT(((INDIRECT("'"&B3&"'!$H$15:$J$379")="TRN")+(INDIRECT("'"&B3&"'!$H$15:$J$379")="SIC")+(INDIRECT("'"&B3&"'!$H$15:$J$379")="LD")+(INDIRECT("'"&B3&"'!$H$15:$J$379")="SAF")+(INDIRECT("'"&B3&"'!$H$15:$J$379")="UN")+(INDIRECT("'"&B3&"'!$H$15:$J$379")="SWP"))*(dlist>=$A$11)*(dlist<=$A$13))

I think.

Ken Puls
2011-11-02, 12:19 AM
I think.

:lol:

JoePublic
2011-11-02, 12:53 AM
Well, I do - sometimes. ;)

Seriously - try typing that formula on a phone and keeping track of the parentheses! :)

Ken Puls
2011-11-02, 05:36 AM
Seriously - try typing that formula on a phone and keeping track of the parentheses! :)

You did that on your phone? Wow... I'm impressed! :nod:

Simon Lloyd
2011-11-02, 07:09 AM
Joe, thanks, looking at loads of those formulae (and it always seems to be when im working nights it's 5:05am here now) gets you all muddled, your shortened version works great and even better when i introduced the rest of my named ranges :)