Adding to a SUMPRODUCT formula

[Mark D]

Afternoon all

I have the below forumula

=(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B22<>1)*(F22="CURRENT"),B22))+(SUMPRODUCT((B22<>1)*(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

 

[Roger Govier]

Hi Mark

Try
=IF(B22="","",(SUMPRODUCT((B22=1)*(F22="EX"),1))
+(SUMPRODUCT((B22=1)*(F22="CURRENT"),2))
+(SUMPRODUCT((B22<>1)*(F22="CURRENT"),B22))
+(SUMPRODUCT((B22<>1)*(F22="EX"),B22)))

 

[Steve Dunn]

=if(b22="","",yourformula)

 

[Glenn]

Afternoon all

I have the below forumula

=(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B22<>1)*(F22="CURRENT"),B22))+(SUMPRODUCT((B22<>1)*(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="CURRENT",2,0)),
IF(OR(F22="EX",F22="CURRENT"),B22,0)))

 

[Mark D]

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((B17<>1)*(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

 

[Bob Phillips]

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

 

[Steve Dunn]

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