=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,<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.

J. Craig Williams

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

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