Combining two formulas
 

I have two formulas that I know to work.

The first looks at a cell and returns to check for two values then
conditionally does one of two things based on the values.

=(OR(H151="business",H151="personal")*(IF(H151="bu siness",A151+30,A151+7)))

The second gives the sum of a range of cells (kindly donated in an
earlier thread).

=SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A3),--(MONTH(Main!$F$3:Main!$F$500)=B3),Main!$D$3:Main!$ D$500)

I


I want a formula that only does the sum if the value in a cell is "yes".

Can anyone help me?

On 2005-04-07 08:59:46 +0100, Redsmartie
said:

I have two formulas that I know to work.

The first looks at a cell and returns to check for two values then
conditionally does one of two things based on the values.

=(OR(H151="business",H151="personal")*(IF(H151="bu siness",A151+30,A151+7)))

The second gives the sum of a range of cells (kindly donated in an
earlier thread).

=SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A3),--(MONTH(Main!$F$3:Main!$F$500)=B3),Main!$D$3:Main!$ D$500)

I

I


I

I want a formula that only does the sum if the value in a cell is "yes".

Can anyone help me?

To answer my own question I think that this works

=IF(Main!G3="Yes",SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A3),--(MONTH(Main!$F$3:Main!$F$500)=B3),Main!$E$3:Main!$ E$500),"")

Which


Which begs the question, why did I use the OR on the first formula? Its
one that I put in a sheet that I've been using for 3 years and I can't
remember why I did it.

Hi

The OR, on the first formula, will give you a '1' if H151 contains
'business' or 'personal'. The * sign between the two halves of the formula
will multiply this '1' by the result of the second half. If H151 does not
contain 'business' or 'personal' then the OR will give you a '0', which
means that the formula result will be zero.

--
Andy.


"Redsmartie" wrote in
message ...
On 2005-04-07 08:59:46 +0100, Redsmartie
said:

I have two formulas that I know to work.

The first looks at a cell and returns to check for two values then
conditionally does one of two things based on the values.

=(OR(H151="business",H151="personal")*(IF(H151="bu siness",A151+30,A151+7)))

The second gives the sum of a range of cells (kindly donated in an
earlier thread).

=SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A3),--(MONTH(Main!$F$3:Main!$F$500)=B3),Main!$D$3:Main!$ D$500)

I

I


I

I want a formula that only does the sum if the value in a cell is "yes".

Can anyone help me?

To answer my own question I think that this works

=IF(Main!G3="Yes",SUMPRODUCT(--(DAY(Main!$F$3:Main!$F$500)=A3),--(MONTH(Main!$F$3:Main!$F$500)=B3),Main!$E$3:Main!$ E$500),"")

Which

Which begs the question, why did I use the OR on the first formula? Its
one that I put in a sheet that I've been using for 3 years and I can't
remember why I did it.