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

killis_

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!

Genevieve P.

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

killis_

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.

Genevieve P.

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

killis_

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

🤔

Genevieve P.

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?

killis_

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?

Genevieve P.

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

killis_

@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 😘