Afternoon all

I have the below forumula

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

Mark D wrote:
Afternoon all

I have the below forumula

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

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)))
--
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((B22<1)*(F2 2="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="","",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((B22<1)*(F2 2="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

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

"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((B22<1)*(F2 2="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

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

"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((B22<1)*(F2 2="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

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

"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((B22<1)*(F2 2="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