=SUMIF Help please:

Heath Sanders

Hi Team,

Formula not working…(#UNPARSEABLE) =SUMIFS[Value of Job],2 [Sold Date:]2,>DATE(2016, 12, 31),[Sold Date:]2,

试图让计算“当月的销售Sold Date” when “Sold Date” matches the current month we are in (January)

• When February rolls over I wanted “Current Month Sales” to roll into “Total Running Sales” Automatically, how do we do this in the sheet?

• I wanted to set up cell that I could reference for previous month sales, i.e if I enter in January when we are in may it will show that sales for January in a cell I set up. I understand that the above formulas have to be set up first.

  Thanks for your help.

J. Craig Williams

A community post:

Heath,


=SUMIFS[Value of Job],2 [Sold Date:]2,>DATE(2016, 12, 31),[Sold Date:]2,

There's lots of things wrong here.

=SUMIFS( [ColumnA]:[ColumnA], << this is a the sum range. You need to designate the column as [Column]:[Column]
[ColumnB]:[ColumnB], < DATE(), << each criteria range is a range. the system will look at the row for you.
(same for the > DATE()) range and criteria
[ColumnC]:[ColumnC], 1 ) << same thing here. Range, not a cell.

You were missing your open and close parentheses for SUMIFS ()

I would change your column name of [Result:] to [Result].
You can see you mistyped it in the formula.

In fact, many of your column names have a colon (:) at the end. Bad practice and not recommended. You seem to use it in cell's so maybe there i a reason I don't know about.

You mistyped the name [Value of Jobs] as [Value of Job]

Assuming you get rid of the colons in the column names, here's the formula:

=SUMIFS([Value of Jobs]:[Value of Jobs], Result:Result, 1, [Sold Date]:[Sold Date], >DATE(2016, 12, 31), [Sold Date]:[Sold Date],
This will return the sum of the [Value of Jobs] column when the Result is 100% and Sold Date is in January.

to avoid retyping next month, you could use:

=SUMIFS([Value of Jobs]:[Value of Jobs], Result:Result, 1, [Sold Date]:[Sold Date], MONTH(@cell)= MONTH(TODAY()))

This will always show 'CURRENT MONTH'. So if you look at it on Feb 1st, anything sold in January won't be in the total.

Previous Month is also possible with a bit of work. And be aware the Jan 2016 and Jan 2017 will show be in the same total unless you account for YEAR.

Craig

Heath Sanders

Hi Craig,

I have totals for some of the rows in first column, so have to start with row 2 ect.

, MONTH(@cell)? Vaule of jobs = $$$, Result = %%%, Sold Date = Calander, Current Month Sales = Value of Jobs when the Sold Date has a January date in it. Shouldnt this formula go into Current Month Sales ????

=SUMIFS([Value of Jobs]2:[Value of Jobs]50, [Result]2:[Result]50, 1, [Sold Date]2:[Sold Date]50, MONTH([(@cell)= MONTH(TODAY()))

Thanks Mate

H

Heath Sanders

Hi Craig,

I have totals for some of the rows in first column, so have to start with row 2 ect.

, MONTH(@cell)? Vaule of jobs = $$$, Result = %%%, Sold Date = Calander, Current Month Sales = Value of Jobs when the Sold Date has a January date in it. Shouldnt this formula go into Current Month Sales ????

=SUMIFS([Value of Jobs]2:[Value of Jobs]50, [Result]2:[Result]50, 1, [Sold Date]2:[Sold Date]50, MONTH([(@cell)= MONTH(TODAY()))

Thanks Mate

H

Heath Sanders

One more thing......I'll put in the year also. This formula is here to stay.

Is it =YEAR(MONTH(TODAY()))) for a matter of interest ?

Thanks Craig

J. Craig Williams

Heath,

If I understand the question, yes, the formula goes in the [Current Month Sales] column.

To add the year, add another range/criteria pair with the same format as your MONTH() formulas and replace MONTH with YEAR.

MONTH() and YEAR() both take dates and return a number for the month and year respectively.

Hope that helps.

Craig

Heath Sanders

Hi Craig,

Thanks again for your help

Is this correct ?

=SUMIFS([Value of Jobs]2:[Value of Jobs]50, Result2:Result50, 1, [Sold Date]2:[Sold Date]50, MONTH(@cell)= MONTH(TODAY(),SUMIFS([Value of Jobs]2:[Value of Jobs]50, Result2:Result50, 1, [Sold Date]2:[Sold Date]50, MONTH(@cell)= YEAR(TODAY()))

J. Craig Williams

Heath,

No.

At a glance, your last criteria should be

YEAR(@cell)= YEAR(TODAY())

There might be more - that's just by looking at it.

If you share it to me, I'll check for sure.

Craig