Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i create a conditional sum? | Excel Worksheet Functions | |||
Help, Multiple conditional calculation | New Users to Excel | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |