Adding to a SUMPRODUCT formula

M

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
 
R

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

Glenn

Mark said:
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)))
 
M

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
 
B

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top