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