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

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet