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")))
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.
Sergei is right. This will drastically affect performance. You should set a reasonable amount of rows in your formulas. i.e. if your formula consists of only 500 rows and 5 columns then Excel only needs to evaluate 2,500 cells. However if you set the whole column Excel needs to evaluate 5,242,880 cells.....
