If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding to a SUMPRODUCT formula
Afternoon all
I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
#2
|
|||
|
|||
Adding to a SUMPRODUCT formula
Hi Mark
Try =IF(B22="","",(SUMPRODUCT((B22=1)*(F22="EX"),1)) +(SUMPRODUCT((B22=1)*(F22="CURRENT"),2)) +(SUMPRODUCT((B221)*(F22="CURRENT"),B22)) +(SUMPRODUCT((B221)*(F22="EX"),B22))) -- Regards Roger Govier Mark D wrote: Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
#3
|
|||
|
|||
Adding to a SUMPRODUCT formula
=if(b22="","",yourformula)
"Mark D" wrote in message ... Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
#4
|
|||
|
|||
Adding to a SUMPRODUCT formula
Mark D wrote:
Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance =IF(B22="","",IF(B22=1,IF(F22="EX",1,IF(F22="CURRE NT",2,0)), IF(OR(F22="EX",F22="CURRENT"),B22,0))) |
#5
|
|||
|
|||
Adding to a SUMPRODUCT formula
Hi Steve, thanks for your help
1 quick question in addition if I may. The formula now reads =IF(B17="","",(SUMPRODUCT((B17=1)*(F17="EX"),1))+( SUMPRODUCT((B17=1)*(F17="CURRENT"),2))+(SUMPRODUCT ((B171)*(F17="CURRENT"),B17))+(SUMPRODUCT((B17 1)*(F17="EX"),B17))) But my slight issue is now this. B17 has numbers ranging from 1-5 , BUT also on accasion can have ""PD"" in there. How would I add to the above forumula +(SUMPRODUCT((B17="pd")*(F17="CURRENT"),B17 I tried it but am getting VALUE come up. I think it may be conflicting with some other part of the formula Thanks again "Steve Dunn" wrote: =if(b22="","",yourformula) "Mark D" wrote in message ... Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
#6
|
|||
|
|||
Adding to a SUMPRODUCT formula
No need for SUMPRODUCT
=IF(B17="","",IF(B17="PD",IF(F17="CURRENT",B17,0), IF(B17=1,LOOKUP(F17,{"CURRENT","EX"},{2,1}),IF(OR( F17="EX",F17="CURRENT"),B17,0)))) -- HTH Bob "Mark D" wrote in message ... Hi Steve, thanks for your help 1 quick question in addition if I may. The formula now reads =IF(B17="","",(SUMPRODUCT((B17=1)*(F17="EX"),1))+( SUMPRODUCT((B17=1)*(F17="CURRENT"),2))+(SUMPRODUCT ((B171)*(F17="CURRENT"),B17))+(SUMPRODUCT((B17 1)*(F17="EX"),B17))) But my slight issue is now this. B17 has numbers ranging from 1-5 , BUT also on accasion can have ""PD"" in there. How would I add to the above forumula +(SUMPRODUCT((B17="pd")*(F17="CURRENT"),B17 I tried it but am getting VALUE come up. I think it may be conflicting with some other part of the formula Thanks again "Steve Dunn" wrote: =if(b22="","",yourformula) "Mark D" wrote in message ... Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
#7
|
|||
|
|||
Adding to a SUMPRODUCT formula
Hi Mark, try this:
=IF(B17="","",IF((B17="PD")*(F17="CURRENT"),"PD", IF(B17=1,(F17="EX")+(F17="CURRENT")*2, ((F17="EX")+(F17="CURRENT"))*B17))) "Mark D" wrote in message ... Hi Steve, thanks for your help 1 quick question in addition if I may. The formula now reads =IF(B17="","",(SUMPRODUCT((B17=1)*(F17="EX"),1))+( SUMPRODUCT((B17=1)*(F17="CURRENT"),2))+(SUMPRODUCT ((B171)*(F17="CURRENT"),B17))+(SUMPRODUCT((B17 1)*(F17="EX"),B17))) But my slight issue is now this. B17 has numbers ranging from 1-5 , BUT also on accasion can have ""PD"" in there. How would I add to the above forumula +(SUMPRODUCT((B17="pd")*(F17="CURRENT"),B17 I tried it but am getting VALUE come up. I think it may be conflicting with some other part of the formula Thanks again "Steve Dunn" wrote: =if(b22="","",yourformula) "Mark D" wrote in message ... Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B 22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B221)*(F2 2="CURRENT"),B22))+(SUMPRODUCT((B221)*(F22="EX") ,B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance |
Thread Tools | |
Display Modes | |
|
|