How do I create a cross-sheet formula for a sum $ total within a date range

I'm trying to calculate the total spend for a customer based on a list of transactions in another sheet in a specific date range. The dates in the sheet I'm trying to reference are classified as dates in the column properties and appear as "1/30/19" for January 30, 2019.

For example, in the sheet in which I'm attempting to place this formula, the first column contains customer names with none repeating. The second column is "total spend in 2019", where I'm attempting to use a formula to calculate this based on the transaction summary sheet, the sheet I'm trying to reference in the formula. Any help is appreciated!

Find more posts tagged with

Sales

Formulas

Comments

Paul Newcome

It would be something along the lines of this:

=SUMIFS(Reference Sheet Range to Sum}, {Reference Sheet Customer Range}, [Customer Name], @{Reference Sheet Date Range}, AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd)))

MP22

Thank you, though I'm not following the date part, specifically the @cell. What would that look like when I fill it in? Do I go back and choose a specific cell? Why would I do that if I've selected the whole column as the range?

Paul Newcome

The @cell reference would stay as is. It basically tells the formula to run on each cell within the range individually.

Paul Newcome

When you fill in the DATE function, you replace the "yyyy" portion with the year, "mm" with the month, and "dd" with the day, all of which would need to be numbers.

MP22

Thanks! I've got that down thanks to your help. Now, I'm trying to have the cell return a sum within a date range, not just after January 1, 2018. Specifically, I want to see the total within the year 2018. I keep trying to add in <=DATE(2019, 1, 1) but it isn't working. Any ideas?

Here is the formula I'm using:

=SUMIFS({Transaction List Net Total}, {Transaction List Customers}, [Customer Name]180, {Transaction List Date Range}, >=DATE(2018, 1, 1))

Paul Newcome

If you are just looking for everything within a specific year, you can skip the DATE function and just use a YEAR function.

=SUMIFS({Transaction List Net Total}, {Transaction List Customers}, [Customer Name]180, {Transaction List Date Range}, YEAR(@cell) = 2018)

MP22

Hm...I just tried this formula and got "#INVALID DATA TYPE"

=SUMIFS({Transaction List Net Total}, {Transaction List Customers}, [Customer Name]109, {Transaction List Date Range}, YEAR(2019))

MP22

I also just tried this and got #INVALID OPERATION

=SUMIFS({Transaction List Net Total}, {Transaction List Customers}, [Customer Name]109, {Transaction List Date Range}, YEAR({Transaction List Date Range} = 2019))

Paul Newcome

Try typing it exactly how I had it to include the @cell reference and placement of parenthesis.

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])