SUMIFS (Current Month Sales)

Heath Sanders

The conditions below formulates when the  "Sold Date" &  "Result" = (100%) in the current month/year (January 2017) When Febuary rolls over the row that calulates ("Current Month Sales") will zero out.

 

I need a formula that captures all the sales for each month (January, Febuary, March....etc). Thinking a formula that calulates each month (Hide it maybe) Then set up a row from that, that calulates all the sales for the year.   How do i continue from the formula below please?   The master Craig W has been a massive help getting me this far.

 

Thanks All

 

=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, YEAR(@cell) = YEAR(TODAY()))))

JamesR

Heather,

 

Not knowing the Structure of your sheet, the method of adding new sales rows and other calculations in the Sheet rows It bis hard to be accurate.  I am a great beliver in KISS.

 

From th information at hand I would consider a Hirarchical solution. 

Level 1 - YTD

Level 2 - Months (*12)

Space

Data Entry Area - 2 locked rows with dummy data and all row calculations.  Use conditional formatting to set formatting for each lever.

 

If data comes in via a Webform set it to come in at the bottom.  It will pick up the formulas needed automaticilly. If entered manually ditto.

 

Once entered Drag and drop into Sales Month.

 

In the Hirearchies set up the formulae using the Children Function to calculate each month and Year to date.

 

Save as template for each year.

 

I hope this helps.

 

James

Heath Sanders

 

Hi James,

 

 Its Heath..., My sheet has three rows with relevant data as follows "Value of Jobs = the dollar value $1234.00, "Result" =50 % percentage, "Sold Date" = 01/10/17 for example. As per the formula below the "Result" has to return 100% and "Sold Date" has to return between 01/01/17 -01/31/17 in January 2017 (MONTH(TODAY() YEAR(TODAY() as its 1/31/17 in New Zealand today. 

 

 

Im unsure of what you mean by KISS and hierarchical solution, I have to be shown examples to learn. My sheet has name, address, product which has no relevance to the formulas at this stage. As I learn how the formulas work in this sheet, I can advance with formulas in the areas if requried. So to sum up, I just need the sales in the current month when criteria is meet (This has been achived) and the total sales for the year which is what we have been discussing.., its just how to set it up and how the formulas are written.

 

One more questions, I have a coloum with sales stage (Drop list), I wanted to add into formula, when i choose"WON" in the SUMIFs critera, I tried  =SUMIFS([column]2:[column]60="WON", [columnA]2:[columnA60],1,......etc didnt like it...how should it look ?

 

 

=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, YEAR(@cell) = YEAR(TODAY()))))

 

Thanks James

JamesR

Hi,

 

KISS stands for "Keep it Simpl Simon"

Hierarchical means structuring your data in a Hierarchy

https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

 

In Accounts (Top Left of Screen) Personal settings set your Local and it should show dates according to your location.

 

Your:

=SUMIFS([column]2:[column]60="WON", [columnA]2:[columnA60],1,......etc didnt like it...how should it look ?

 

Should be:

=SUMIFS([column]2:[column]60,"WON", [columnA]2:[columnA60],1,.Each Paramiter seperated by commas.

 

Your:

=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, YEAR(@cell) = YEAR(TODAY()))))

 

It is illogical. You cannot nest a Sumifs inside another Sumifs like that.  What does the resukt you want look like?  Give me that and I may be able to work back to the Formulae.

 

 

Go here:

https://help.smartsheet.com/articles/775363-using-formulas

Scroll down to find the link to "Smartsheet Formula Examples" Template.  This will help you with yor Formula Structures and usage.