On 2005-05-23 14:23:04 +0100, "Duke Carey"
said:

Maybe:
=SUMPRODUCT(--(other
range="yes"),--(DAY(Main!$F$3:Main!$F$500)=A5),--(MONTH(Main!$F$3:Main!$F$500)=B5),Main!$C$3:Main!$ C$500)

"Martin

Smith" wrote:

I have the following formula giving me a total value of a range of
figures based on date:

=SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A5),--(MONTH(Main!$F$3:Main!$F$500)=B5),Main!$C$3:Main!$ C$500)

I
want

the totalisation to be done only if yes appears in another column. I
have used logic functions in the past but I am struggling to use it in
the above formula without it flagging an error or using the IF function
and I put the formula in for the true response it shows the formula as
text rather than doing the calculation.

On my main sheet the "yes" and "no" are from G7 onwards but the formula

=SUMPRODUCT(--(Main!$G$7:Main!$G$500="yes")--(DAY(Main!$F$7:Main!$F$504)=A9),--(MONTH(Main!$F$7:Main!$F$504)=B9),Main!$C$7:Main!$ C$504)

Gives

a result of #N/A