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

Adding to a SUMPRODUCT formula
 

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

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

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

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

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

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