Adding to a SUMPRODUCT formula

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
0
Utf
5/10/2010 2:44:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
445 Views

Similar Articles

[PageSpeed] 56

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((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
0
Roger
5/10/2010 3:01:15 PM
=if(b22="","",yourformula)

"Mark D" <MarkD@discussions.microsoft.com> wrote in message 
news:44DFEE74-B218-489C-8F5F-E8456C715413@microsoft.com...
> 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 

0
Steve
5/10/2010 3:01:23 PM
Mark D wrote:
> 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)))
0
Glenn
5/10/2010 3:01:47 PM
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" <MarkD@discussions.microsoft.com> wrote in message 
> news:44DFEE74-B218-489C-8F5F-E8456C715413@microsoft.com...
> > 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 
> 
0
Utf
5/10/2010 3:31:01 PM
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" <MarkD@discussions.microsoft.com> wrote in message 
news:8C8D8ADA-9377-40E9-94A7-458697A1AAD1@microsoft.com...
> 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" <MarkD@discussions.microsoft.com> wrote in message
>> news:44DFEE74-B218-489C-8F5F-E8456C715413@microsoft.com...
>> > 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
>> 


0
Bob
5/10/2010 5:23:56 PM
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" <MarkD@discussions.microsoft.com> wrote in message 
news:8C8D8ADA-9377-40E9-94A7-458697A1AAD1@microsoft.com...
> 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" <MarkD@discussions.microsoft.com> wrote in message
>> news:44DFEE74-B218-489C-8F5F-E8456C715413@microsoft.com...
>> > 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
>> 

0
Steve
5/11/2010 8:10:51 AM
Reply:

Similar Artilces:

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Adding Contacts Folder as Address Book in Exchange Server
I am trying to add my contacts folder to my address book in Outlook XP and don't see the Tools, Options I used to see in Outlook 2000. How can I add this folder to my address book? Is that an option you can turn on or off for a profile in Exchange Server? Thanks!! -- Remove 'spam' from email address to contact me directly Right click on your Contacts folder, go to properties, outlook address book. Can you tick the box there? If not, go to tools, e-mail accounts, view/change address books, and make sure you have the Outlook Address Book option there. Then try right-clicking...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Adding an "I'm out of the Office" Message
Re Outlook Express. I can't find anywhere on the index about how to send one of those "I will be out of the office from July blah blah to August blah blah." Anyone help, please? thanks-- Richard Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexp...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Sumproduct usage?
My brain's on overload at the moment, so I'm turning to you guys for help. I need help w/ a formula that will count the number of sales that someone had based on a certain date...I thought I could use sumproduct, but I'm beginning to wonder if I was wrong about that. The relavant columns are D & M. Col D Col M 12/24/2009 Andy 12/24/2009 Charles 12/24/2009 Andy 12/25/2009 Charles You had the right function in mind. Try something like this... Use cells to hold the criteria: A1 = some date B1 = some name =SUMP...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Adding GAL users into a custom form
Hi, I have created a custom form, and am required to add a series of approvers. What I am trying to achieve is: * Add users from my GAL into a text field, so that when the form is sent, they do not get initially CC'ed the form. Is this achievable, and if so, how do I do it (if you could help by including any appropriate code, that would be great). Regards, Rick ...

Non AD emails going to 1 user
I have an Exchange 2003 server running on SBS 2003 the issues is one user is getting all the emails sent to him that look like they are coming from his domain. For example his email is user@mydomain.com but in his inbox he is getting XYZ@mydomain.com but XYZ is not in the AD or has a email box set up on this server. Why is getting this non AD email and how can I stop it. Thanks in advance Are you sure it's not a SPAM where the spammer may have simply put in 123abc@mydomain.com and BCC it to all possible conceivable names @mydomain.com?? R Green "LaOVis" <LaOVis@discuss...

Tiebreaker in a Index formula?
I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...

locking / unlocking cell in formula
E F 1 Do you own a guitar validation cell (yes/no) 2 If yes is it a Gibson 3 Is it a 5 string 4 Is it electric 5 Do you own a piano 6 Is it electric 7 Does it have 88 keys 8 Do you own a TV 9 Is it color 10 Is it an LCD 11 Is it 25" 12 Is it 32" 13 Is it 42" 14 Do you own a radio I would like to lock cells F2:F4 if F1 contains the word "no" I would also like to lock cells F6:F7 if F5 contains the word "no" I would also like to lock ce...

plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...

display result of formula
How do I make a cell which contains a formula display the result of that calculation rather than the formula (it is formatted as "number" and I have tried "recalculating") Peter Peter Chadbund expressed precisely : > How do I make a cell which contains a formula display the result of that > calculation rather than the formula (it is formatted as "number" and I have > tried "recalculating") > Peter Change the cell format to 'General'. Redo the formula. Change the cell format to 'Number' and apply your display preferenc...

User Form Formula ?
Hi On a user form I have 2 Text Boxes in which to enter cash figures Excluding TAX - on the worksheet these 2 figures enter columns E & G - in column K I have the formula =IF(SUM(E3+G3)=0"", SUM(E3+G3)*17.5% - If I create a new Text Box on the user form could it generate the Tax as the formula above and then on clicking Add Iformation Button it would enter column K thus doing away with the formula in column K on the worksheet which is sometimes altered by mistake. Any help much appreciated Cheers ---- Mully Yes, you could add TextBox3.Text = Format(CStr((Val(Tex...

Excel 2003 Formula Error
Bit of a strange one this..... I have been sent a workbook which tracks staff absences, holidays etc. There is a fomula which calculates the total number of days off, holidays etc. by summing up a specific cell value from each month tab of the workbook. This works fine but if a user changes one of these cell values (from blank to 1 for example) but then decides to delete the number entered (leaving the cell blank again) the calculated "Sum" cell on the summary sheet then displays a "#Value" error. Try as I might I cannot replicate this error in Excel 2...

Formula Help Needed #2
I would greatly appreciate any help anyone can offer on this. Ok let me first give a little background info. I have a spreadsheet of my raw data (spreadsheet a) it has 4 categories, account#, client name, balance amount and a code (1-7). The code tells me what internal business category each account falls under. I have another spreadsheet (spreadsheet b) that takes the raw data of a. and puts it into a neat easy to read report. What I need is a formula that will look through the entirety of spreadsheet a and first, determine let’s say how many code 1’s there are ( I know this can be a...