SUMPRODUCT issues

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))
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
...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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
No worries - been there! ;)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
... 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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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 ;)
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
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.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
Well, I do - sometimes. ;)

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