Sumproduct returns 0

[jwah]

Objective is to reference the sheet 'data' which has dd-mm-yyyy in column A and currency values in column I. Seems simple enough but returns 0.

The formula resides in e.g. C40 (C41, C42 etc) and is
=SUMPRODUCT((TEXT(data!$A$2:$A$100,"mmm-yy")=TEXT($B40,"mmm-yy"))*(data!$A$2:$A$100=$B40),data!$I$2:$I$100)

B40 has Mar-11 and there a number of rows in the sheet data that have a value in Column I with a March date.

 

[Haseeb Avarakkan]

Hello,

you are almost there. Just remove the 2nd condition (data!$A$2:$A$100=$B40). You already have this in first condition. Try this,

=SUMPRODUCT(--(TEXT(data!$A$2:$A$100,"mmm-yy")=TEXT($B40,"mmm-yy")),data!$I$2:$I$100)

Assume data!I2:I100 is the valid numbers.

 

[jwah]

Hi Haseeb,
Fixed many thanks. (I copied an old formula and SUMPRODUCT is not one I use often). cheers