Sumif with format problem

[drluke]

I'm trying to use a sumifs:

SUMIFS('Sheet 1'!$R:$R,'Sheet 1'!$A:$A,A1,'Sheet 1'!$C:$C,$G$6)

A1 in my first lookup criteria is the month of the year written as 1 for Jan, 2 for Feb etc. However in Sheet 1 the months of the year is written in the yyyy/m format for Jan-Sept, and yyyy/mm for Oct-Dec.

How can I change/add to the sumifs to ignore the yyyy when it looks at that criteria?

 

[jtakw]

Hi,

Try this:

=SUMPRODUCT((MONTH('Sheet 1'!$A:$A)=A1)*('Sheet 1'!$C:$C=$G$6)*'Sheet 1'!$R:$R)

I strongly suggest Not using Entire Column references (that's over 1 million rows for the formula to work on), but limit the range to something you Know is larger than you need, i.e. $A1:$A10000