Reply With QuoteHoly 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...
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 criteriaA11 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 appreciatedOriginally Posted by Simon Lloyd
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, CMA, MS MVP (Excel)
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.
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 05:45 PM. Reason: edited spelling - still wrong i guess :)
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, CMA, MS MVP (Excel)
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.
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
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, CMA, MS MVP (Excel)
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.
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.
Posting Permissions
Bookmarks