Sum ifs

[boll55]

Book11.xlsx

I would like to find a formula that will sum up the total amount of oil for the last 6 months, year, 3 months and month for each driver. some drivers will not have any oil. I want the same for additives.

I already have a coloum for oil and additives for each driver. Please ignore these because I want them there for other reason. This is total on the sheet. I have a few lables set up where the fomula needs to appear

[dilipandey]

Hi boll55,

I have checked and found that you have already have sumif formula in column P and Q.. what difference you need there or do you need some other formula? thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Pete_UK]

Not sure if you need the amounts or the quantities, but you can put this in S6 for the 6-month costs:

=SUMPRODUCT(($C$6:$C$232=O6)*($B$6:$B$232>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))*($B$6:$B$232<=TODAY()),(($F$6:$F$232)+($I$6:$I$232)))

and change the F and I ranges at the end if you need quantitites. Similar formula for one-year costs:

=SUMPRODUCT(($C$6:$C$232=O6)*($B$6:$B$232>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*($B$6:$B$232<=TODAY()),(($F$6:$F$232)+($I$6:$I$232)))

Copy these down as required.

Hope this helps.

Pete

[boll55]

On my attached sheet I want to sum the oil (F) colum IF the persons names (C) matches the driver coloum (O) and the date (B) is within 6 mothns of todays date (M2) this goes in coloum (S) and in the nexT coloum (T) i want the same thing but for the last year. The following colum (U) I will combind (S) and (T) for a total. In colum (V)-(X) I want the exact same thing but for additives (I)

=SUMIFS($I$6:$I$1400,$C$6:$C$1400,O6,$B$6:$B$1400,$N$2)

this is the formula i have tired but its not working.

all values should be sumed since there are no dates past 6 mothns or 1 year

[Pete_UK]

You wont be able to use SUMIFS if you have Excel 2003, or is your profile a bit out of date now?

I didn't see any other labels beyond column T, so I thought you just wanted the totals (oil + additives) for the time periods.

Pete

[boll55]

Yes thats what I needed! Is there a way to make additives and oils sperate? and how do i make it for the current month also? Ive tired 1 and 0 but it gave me september and no results

[Pete_UK]

Instead of this at the end of the formula:

... ,(($F$6:$F$232)+($I$6:$I$232)))

you can change it to this:

...,($F$6:$F$232))

for the oil, and column I for the additives.

Yes, change the -6 to -1 to get the totals for the last month.

Hope this helps.

Pete

[boll55]

I want it for the current month of october

[Pete_UK]

Well, leave the -1 out altogether then.

Pete