=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