Calculation conditional on yes no
 

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.

One way:

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



In article ,
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.

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 2005-05-23 14:11:50 +0100, Martin Smith
said:

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.

Thanks for the suggestions.

I had run the formula on a "calc" sheet then did a separate logic
formula but your suggestions are much cleaner and efficient.

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

On 2005-05-26 16:18:56 +0100, Martin Smith
said:

UMPRODUCT(--(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

I think that there was a missing comma

=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)

But

it still gives #VALUE!

On 2005-05-26 16:22:18 +0100, Martin Smith
said:

On 2005-05-26 16:18:56 +0100, Martin Smith
said:

UMPRODUCT(--(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

I think that there was a missing comma

=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)

But
it

still gives #VALUE!

Solved it. Sorry for the premature posts. the G500 reference must be
G504 in line with the other range selections