Forum Discussion
EXCEL forumla - SUM IF / OR / AND
I need some help with a forumla;
I work in transport and I want to determine how many km's a particular asset has done, we already use a spreadsheet for loading records which includes the km's travelled for a whole unit (truck and trailers);
KM's appear in column AB
Each asset appears in column M,N,O,P,Q,R or S it can appear in either depending on the combination used, but only once per row (so prime mover 1 with trailer 1 etc. or prime mover 3 with trailers 9, 10 & 11)
This formula works if i put the asset number in A18 but is clumsy ...
=SUMIF('Data Sheet'!$M:$M, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$N:$N, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$O:$O, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$P:$P, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$Q:$Q, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$R:$R, A18,'Data Sheet'!$AB:$AB)+SUMIF('Data Sheet'!$S:$S, A18,'Data Sheet'!$AB:$AB)
What I then want to do is narrow the search down to produce KM travelled per asset per QUARTER the date appears in column C;
=SUMIFS('Data Sheet'!$AB:$AB, 'Data Sheet'!$C:$C, ">30/09/2017", 'Data Sheet'!$C:$C, "<01/01/2018") I can't write the correct forumla to work with the columns, maybe because i'm not getting the OR part to work, I want to check each column for asset number but not exclude that row if it doesn't appear (ie Trailer 1 might appear in either column N,O,P per row but not all)
Any help is appreciated - thank you!
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")))
