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
Hi Robyn,
I guess you mean $M:$S and similar for other ranges. When formula will be incorrect for the headers (row 1) and is to be adjusted. Plus that's not good for the performance.
Better if you change 29 on, let say, 29000 everywhere in the formula, e.g. $M$2:$S$29000, etc.
