Hello, Excel Gurus!
I need a formula that can add the total fuel costs for specific vehicles listed in a range of cells based upon what is in two columns, i.e., 'Add the fuel costs together for vehicle ABC DEF if the amounts in cells E5 to E9 are greater than 0 AND the vehicle ID is "ABC DEF". I'd need to have a formula for each vehicle I'd want to track, but in the instances referenced in the image below, the results would be:
I tried combining the SUMIFS formula with the use of AND, but my results would always end up being either "True" or "False" instead of the sum of fuel costs for each particular vehicle over the course of the month. I think I'd need to use an array formula to account for the range of cells. But the bottom line is that I need to be able to compare each row to determine if the Amount is greater than 0 and the Vehicle ID is included. If those two conditions are true, then I'd need to add the fuel cost for that specific vehicle. This evaluation would need to be applied to each row of the spreadsheet so that at the end of my report I can use the formula to display how much total money for the month was spent on fuel for each of the respective vehicles. I hope I've explained it well enough. If not, let me know.
I thank you for any help you can provide. It is greatly appreciated.
I need a formula that can add the total fuel costs for specific vehicles listed in a range of cells based upon what is in two columns, i.e., 'Add the fuel costs together for vehicle ABC DEF if the amounts in cells E5 to E9 are greater than 0 AND the vehicle ID is "ABC DEF". I'd need to have a formula for each vehicle I'd want to track, but in the instances referenced in the image below, the results would be:
- Vehicle "ABC DEF" = $400.00 (total fuel cost for the month)
- Vehicle "123 456" = $450.00 (total fuel cost for the month)
I tried combining the SUMIFS formula with the use of AND, but my results would always end up being either "True" or "False" instead of the sum of fuel costs for each particular vehicle over the course of the month. I think I'd need to use an array formula to account for the range of cells. But the bottom line is that I need to be able to compare each row to determine if the Amount is greater than 0 and the Vehicle ID is included. If those two conditions are true, then I'd need to add the fuel cost for that specific vehicle. This evaluation would need to be applied to each row of the spreadsheet so that at the end of my report I can use the formula to display how much total money for the month was spent on fuel for each of the respective vehicles. I hope I've explained it well enough. If not, let me know.
I thank you for any help you can provide. It is greatly appreciated.
