Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining formulas and results HELP!! | New Users to Excel | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |