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