Sum

[billykiller05]

I am trying to create a formula on a Sheet 1 to Sum data on Sheet 2. I need to sum column M if the Month in Column C is January. I have tried the following formulas and I am getting an error or 0.

=SUM(IF(MONTH(Sheet2!C2:C25)=1|Sheet2!M2:M25|0))
=SUMPRODUCT(MONTH(Sheet2!C2:C25)=1|Sheet2!M2:M25)
=SUMIF(Sheet2!C:M|MONTH(Sheet2!C:C)=1|Sheet2!M:M)

[Richard Buttrey]

Hi,

Please Login or Register  to view this content.

This assumes column C contains month names rather than the numbers 1-12 representing January-December. If not change the criteria to the value 1 rather than "January".

[Jonmo1]

The SUMIF won't work, because you can't manipulate the criteria range with another function like MONTH.
It can only work with the data in the cell exactly as it exists.

The SUMPRODUCT doesn't work because you need to coerce the True/False values to 1/0 values
=SUMPRODUCT(--(MONTH(Sheet2!C2:C25)=1)|Sheet2!M2:M25)

The SUM(IF formulas should work (if it's entered with CTRL + SHIFT + ENTER)
If it's not, it's either of the following 3 problems.

1) Dates in column C are NOT really dates, they're text strings that look like dates.
Test for this with =ISNUMBER(Sheet2!C2) and fill down
TRUE = Date, FALSE = TEXT string.

2) The numbers in column M are not really numbers, just text strings that look like numbers (also called "Numbers Stored As Text")
Also test with
=ISNUMBER(Sheet2!M2) and filled down.

3) The formula was not entered with CTRL + SHIFT + ENTER

[billykiller05]

The CTRL + SHIFT + ENTER works with =SUM(IF(MONTH(Sheet2!C2:C25)=1|Sheet2!M2:M25|0)) however if increase C2:C25 to C:C and M2:M25 to M:M to include all possible cells I get an error.

[Jonmo1]

however if increase C2:C25 to C:C and M2:M25 to M:M to include all possible cells I get an error.

I would assume you have Text headers in row 1
MONTH(text) = #Value! Error

I highly reccommend NOT using entire column references in an array formula.
Because excel will do the work on every single row on the sheet.
That creates ALOT of unnecessary work on blank rows.

Stick to a defined set of rows.

Pick a reasonable number that represents the most rows you expect to ever have, and add 10%

[billykiller05]

Ok, that works. Thank you very much.

[Jonmo1]

You're welcome.