#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# =SUMIF Help please:

Options
edited 12/09/19

Hi Team,

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

Trying to get “Current Month Sales” to calculate “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.

Tags:

• ✭✭✭✭✭✭
Options

A community post:

Heath,

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

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], <DATE(2017, 2, 1))

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

• Options

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

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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()))

• ✭✭✭✭✭✭
edited 01/19/17
Options

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

This discussion has been closed.