SUMPRODUCT issues

Simon Lloyd

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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
Simon Lloyd said:
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))
Click to expand...
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 :)
 
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... ;)
 
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 ;))
 
Last edited:
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 "
 
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 :)
 
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
 

Attachments

  • shift Diary test.xls
    184.5 KB · Views: 19
Simon Lloyd said:
...for january it could bo 01/01/2011 and in A13 08/01/2011
Click to expand...

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.
 
Hi ken i believe im using the AND operator *, but i see where you're coming from (even with your US date format :))
 
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).
 
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.
 
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 :)
 
No worries - been there! ;)
 
Simon Lloyd said:
... 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 :)
Click to expand...

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!
 
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 ;)
 
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!
 
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))
Click to expand...
Any help appreciated
 
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.
 
Well, I do - sometimes. ;)

Seriously - try typing that formula on a phone and keeping track of the parentheses! :)
 
You must log in or register to reply here.
Back
Top