I need help calculating the sum of one column based on a date in another column
OK, so I'm trying to calculate values (in one column) based on the Month (in another column). This is what I have tried:
=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, MONTH(@cell) = MONTH(TODAY(), {Sheet - Sales Pipeline Range 3}))
However, it returns 0. I'm referencing another sheet if that makes a difference.
Not sure what I'm doing wrong, hope you can help!
Best Answer
-
Hi @killis_
The formula is structured correctly! And yes, your column names are spelled the same as your first screen capture. However it looks like you're writing the formula in a second sheet, separate from where the columns are located. Is that correct?
If so, you'll need to use {cross sheet references} instead of [in-sheet].
Here's documentation on references in formulas: Create a Cell or Column Reference in a Formula and Formulas: Reference data from other sheets
In this case, you'll want a structure like so:
=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I've expanded on this a little:
=SUMIF([{Expected Close Date}]:[{Expected Close Date}], MONTH(@cell) = 11, [{Forecast Amount}]:[{Forecast Amount}])
Getting UNPARSABLE. Know I'm doing something wrong, I just can't work it out.
-
Hi @killis_
For your first formula, it looks like you're missing a closing parentheses after the TODAY() function, to close out your MONTH( ___):
=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, MONTH(@cell) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})
For your second formula, can you clarify if you're putting this formula in the same sheet as your columns or are you looking into a separate sheet? You're combining ways of referencing formulas. If you're looking in the same sheet, there's no need for {curly brackets}. See: Create a Cell or Column Reference in a Formula
Try:
=SUMIF([Expected Close Date]:[Expected Close Date], MONTH(@cell) = 11, [Forecast Amount]:[Forecast Amount])
I will also note that the MONTH function can error when it hits a cell with a blank value or text value. To get rid of this, I would suggest wrapping it in an IFERROR, like so:
=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})
OR
=SUMIF([Expected Close Date]:[Expected Close Date], IFERROR(MONTH(@cell), 0) = 11, [Forecast Amount]:[Forecast Amount])
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you for coming back to me!
I have tried each option, and I am still getting UNPARSEABLE.
I can confirm I am using the same sheet to reference from.
🤔
-
Hi @killis_
Thanks for this screen capture, this helps! I can see that the column names are not highlighting in colours - this to me indicates there could be a slight difference in how the column is spelled versus what's in your sheet, is that possible?
Can you paste a screen capture of only your column names?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. , please see the column names:
I see there is a difference in the column name so have updated it, but I still get UNPARSEABLE:
I know I'm so very close, could it be to do with the sheet referencing?
-
Hi @killis_
The formula is structured correctly! And yes, your column names are spelled the same as your first screen capture. However it looks like you're writing the formula in a second sheet, separate from where the columns are located. Is that correct?
If so, you'll need to use {cross sheet references} instead of [in-sheet].
Here's documentation on references in formulas: Create a Cell or Column Reference in a Formula and Formulas: Reference data from other sheets
In this case, you'll want a structure like so:
=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. You are wonderful!
Thank you so much it worked, I'm so grateful, been wrestling with that for days!
Have a great day, Holly 😘
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!