Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 376
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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)))
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a criteria to sumproduct Diddy Excel Worksheet Functions 5 January 19th 09 10:32 AM
Sumproduct multiplying instead of adding denise Excel Worksheet Functions 4 June 15th 07 09:26 PM
Adding "OR" to a Sumproduct Formula carl Excel Worksheet Functions 0 March 21st 06 07:43 PM
Adding "OR" to a Sumproduct Formula Duke Carey Excel Worksheet Functions 0 March 21st 06 07:41 PM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"