Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: SUMPRODUCT issues

  1. #1
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    Excel 2016

    SUMPRODUCT issues



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Quote Originally Posted by Simon Lloyd
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    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 by Simon Lloyd; 2011-10-21 at 06:45 PM. Reason: edited spelling - still wrong i guess :)
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    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 "
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    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
    Attached Files Attached Files
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Simon Lloyd View Post
    ...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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    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 )
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    400
    Articles
    0
    Excel Version
    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).
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  10. #10
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    191
    Articles
    0
    Excel Version
    2010
    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.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •