Forum Discussion
EXCEL forumla - SUM IF / OR / AND
You can use a SUMPRODUCT() formula like this to solve your issue (Please see attached .xlsx) :
=SUMPRODUCT(($M$2:$S$29=AD4)*($AB$2:$AB$29)*($C$2:$C$29>=DATEVALUE("01/01/2017"))*($C$2:$C$29<=DATEVALUE("03/31/2017")))
Robyn-
Hope you're doing well. Would it be possible for you to put together a non-sensitive mockup of your scenario. Sometimes describing things in formula text can be difficult to comprehend. A simple before and after would suffice with a few rows of data. What happens now? What do you expect to happen? Your inquiry sounds interesting. I'm sure someone on the forum would love to assist you with a little more detail.
Hi Matt,
does this make more sense, below is the data sheet with the relevant columns displaying the the fleet numbers - M through S and KM travelled at AB
The below is something like i'm trying to achieve, an overall report of the KM Travelled per unit in the financial quarter.. I can calculate a overall KM's but not defined by a particular date...
Any help or advice is appreciated.
You can use a SUMPRODUCT() formula like this to solve your issue (Please see attached .xlsx) :
=SUMPRODUCT(($M$2:$S$29=AD4)*($AB$2:$AB$29)*($C$2:$C$29>=DATEVALUE("01/01/2017"))*($C$2:$C$29<=DATEVALUE("03/31/2017")))
Thanks Matt, that is excellent!
Do you know if when using an array - can you use the whole column range? i.e $M$M:$S$S instead of $M$2:$S$29 or does the rows need to be specified (and adjusted if additional rows are added)
thank you
