I need help calculating the sum of one column based on a date in another column

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

All comments

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.

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})

=SUMIF([Expected Close Date]:[Expected Close Date], IFERROR(MONTH(@cell), 0) = 11, [Forecast Amount]:[Forecast Amount])

Cheers,

Genevieve

Hi @Genevieve P.

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.

Screenshot 2022-11-17 at 15.34.11.png

🤔

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:

Screenshot 2022-11-17 at 16.32.03.png

I see there is a difference in the column name so have updated it, but I still get UNPARSEABLE:

Screenshot 2022-11-17 at 16.35.00.png

I know I'm so very close, could it be to do with the sheet referencing?

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