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 [insheet].
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
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

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?

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 [insheet].
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

@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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!