Using Arrays With IF functions

Hi all, I am running into a problem. I am trying to sum values in a
table using arrays and I get the VALUE error.  My formula is something
like this.

{=SUM((A1:A10=A15)*(B1:B10=B15)*(C1:C10))}

The values in A1:A10 and B1:B10 are manually inputted. The C1:C10 are
calculated using an IF functions and has some greater than, less than
equations within. My goal is to get the A and B columns to equal TRUE
and then multiply the C column and the Sum them all together.

I think my problem is the C column but not sure. Can this error be
cause by the IF function?  I even tried making a new table, linking the
values from the original table to omit the large formulas - but I
keep getting the same error message.  Any suggestions?  Thanks for your
time.

0
10/31/2006 12:23:08 AM
excel 39879 articles. 2 followers. Follow

2 Replies
541 Views

Similar Articles

[PageSpeed] 51

Daniel,

For your formula to work, first of all, you need to enter it as an array 
formula -- press Ctrl-Shift-Enter, not just Enter.  It will only sum the 
values in C1:C10 where the cell in A1:A10 in the same row equals A15 , and 
the same for the value in B1:B10.  Is this your objective?

You could also use this, which does not require entering as an array 
formula:

=SUMPRODUCT((A1:A10=A15)*(B1:B10=B15)*(C1:C10 ))

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<carlsondaniel@gmail.com> wrote in message 
news:1162254188.257362.113250@e3g2000cwe.googlegroups.com...
> Hi all, I am running into a problem. I am trying to sum values in a
> table using arrays and I get the VALUE error.  My formula is something
> like this.
>
> {=SUM((A1:A10=A15)*(B1:B10=B15)*(C1:C10))}
>
> The values in A1:A10 and B1:B10 are manually inputted. The C1:C10 are
> calculated using an IF functions and has some greater than, less than
> equations within. My goal is to get the A and B columns to equal TRUE
> and then multiply the C column and the Sum them all together.
>
> I think my problem is the C column but not sure. Can this error be
> cause by the IF function?  I even tried making a new table, linking the
> values from the original table to omit the large formulas - but I
> keep getting the same error message.  Any suggestions?  Thanks for your
> time.
> 


0
someone798 (944)
10/31/2006 1:55:50 AM
Hi Earl,

The product won't work because I am trying to extract and sum certain
values. I finally figured out what was wrong. I did not have any values
in some of the A and B columns so that is why it kept returing VALUE.
Thanks for your help!

Dan

Earl Kiosterud wrote:
> Daniel,
>
> For your formula to work, first of all, you need to enter it as an array
> formula -- press Ctrl-Shift-Enter, not just Enter.  It will only sum the
> values in C1:C10 where the cell in A1:A10 in the same row equals A15 , and
> the same for the value in B1:B10.  Is this your objective?
>
> You could also use this, which does not require entering as an array
> formula:
>
> =SUMPRODUCT((A1:A10=A15)*(B1:B10=B15)*(C1:C10 ))
>
> --
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> <carlsondaniel@gmail.com> wrote in message
> news:1162254188.257362.113250@e3g2000cwe.googlegroups.com...
> > Hi all, I am running into a problem. I am trying to sum values in a
> > table using arrays and I get the VALUE error.  My formula is something
> > like this.
> >
> > {=SUM((A1:A10=A15)*(B1:B10=B15)*(C1:C10))}
> >
> > The values in A1:A10 and B1:B10 are manually inputted. The C1:C10 are
> > calculated using an IF functions and has some greater than, less than
> > equations within. My goal is to get the A and B columns to equal TRUE
> > and then multiply the C column and the Sum them all together.
> >
> > I think my problem is the C column but not sure. Can this error be
> > cause by the IF function?  I even tried making a new table, linking the
> > values from the original table to omit the large formulas - but I
> > keep getting the same error message.  Any suggestions?  Thanks for your
> > time.
> >

0
10/31/2006 5:46:14 PM
Reply:

Similar Artilces:

Learning to Use Word 2007 as a Blind Person
I'm transitioning from Word 2000 to 2007 and would like to know if there's a way for a blind user like me using the screen-reading program called JAWS 10 )from Freedom Scientific) to learn Word 2007 quickly and thoroughly. Thank you for your feedback! I don't mean to be dismissive, but sighted people have a lot of trouble making the transition from the older Word interface to that of 2007. I don't know anything about the screen reading program, but frankly I don't see it helping you learn 2007 *quickly*. It took me over a year to become comfortable with 200...

How do I run a rule by using a toolbar icon?
I have Outlook 2007 and have a rule set up to delte spam by detecting certain words in the spam. Here is what someone gave me but it does not work: Sub RunRuleDeleteSpam() Dim oNS As Outlook.NameSpace Dim oStore As Outlook.Store Dim colRules As Outlook.Rules Dim oRule as Outlook.Rule Set oNS = Application.GetNameSpace("MAPI") Set oStore = oNS.DefaultStore Set colRules = oStore.Rules Set oRule = colRules.Item("Delete Spam") oRule.Execute End Sub If I run it it highlights the set oRule line and stops, I guess. I know n...

2-dimensional array
How do I create an array like the following which contains string and integer? string[,] siblings = { {"Mike", 28}, {"Mary", 25}, {"John", 31} }; object[,] siblings = { { "Mike", 28 }, { "Mary", 25 }, { "John", 31 } }; Jason Newell www.jasonnewell.net Rick wrote: > How do I create an array like the following which contains string and integer? > > string[,] siblings = { {"Mike", 28}, {"Mary", 25}, {"John", 31} }; > > > thanks "Jason Newell" wrote...

Allow work order deposits to be paid for using store credits
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=ed16e74f-2d3a-4689-9444-cfa2cb09e82a&dg=microsoft.public.pos ...

Using E2k7 Mgmt Shell to create resource (rooms, equip) mailboxes
Good Day, I've got an OU with disabled "users" that I would like to create room mailboxes for. I've successfully created regular user mailboxes using the same cmdlet but am unable to figure out the correct syntax and variable changes for creating room mailboxes. The cmdlet is as follows: get-user -organizationalUnit "E2k7 - Test Conf Rooms" | where-object{$_.RecipientType -eq "User"} | Enable-Mailbox -Database "MBXservername\mailbox database" | get-mailbox | select name,windowsemailaddress,database When run on an OU of regular users, thi...

Using a shared calendar in Outlook 2003
The shared calendar was setup in Outlook 2000 on another machine. Am I able to gain access to the calendar or no? I tried to install the net folders component but it seems it's not available for 2003, is this true? Thanks for your help. Yes, net folders has been dropped since Outlook 2002. There are a lot of third party tools that can do it when you don't connect to an Exchange server. Share just the Calendar; http://tinyurl.com/3p6xm Share all Outlook folders http://tinyurl.com/49lzg Also take a look here http://www.slipstick.com/outlook/share.htm -- Robert Sparnaaij [M...

cube functions
Hello everyone, in Excel 2007 we have the new cube functions described in this blog for example: http://blogs.msdn.com/excel/archive/2006/02/02/the-excel-12-blog-rides-again-or-cube-functions-part-1.aspx They are much slower when opening or refreshing workbooks compared to regular pivot tables. I'd like to know if there is any whitepaper out there about performance and scalability of cube functions. Thank you very much, Robert -- The sun is shining. ...

Email Functionality
Hi, In the help menu doc, it says the following Recipients of your e-mail will have the option of either requesting more information (thus becoming a lead) or requesting that they be removed from your e-mail list. Is this a built in feature or do i have to do some customisation in other to get this functionality. Has any one got further infomation on this. Kind Regards Abby ...

need help writing function
Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 Hi, Let's assume th...

What happened to the functionality of the ObjModel header files?
I'm porting a MFC application from MSVC 6.0 to MSVC 7.1. In our older version, our code makes use of several header files from the ObjModel directory. These directories have been eliminated in the 7.1 version. Where can we find the functionality that was part of these files? ...

Implementing an html functionality using MFC application
Sir, I have a web site which has mainly a TextArea and an OK button which sends the message entered in the text area to the site. Can i create an MFC application by which when i push a button the same message should be send to the desired site.. Yes, it is possible using wininet functions. Can you post your webpage? -- Vipin Aravind Microsoft MVP "rocky" <kirenmukherjee@gmail.com> wrote in message news:1137390403.945426.143660@g49g2000cwa.googlegroups.com... > Sir, > I have a web site which has mainly a TextArea and an OK button which > sends the message entered ...

Req: Formula Array.....
Hi all, Have an XLS and trying to generate stats from it. On one Worksheet I would like to break up stats in the following manne (for example).. Code ------------------- Ongoing Closed July Client Praises ?? ?? Complaints ?? ?? Vendor Praises ?? ?? Complaints ?? ?? Partner Praises ?? ?? Complaints ?? ?? The data contained on another Worksheet is in the following format: Col#01 - Date (Format: `dd - mmm - yy`) Col#02 - T...

How to use global function
Hi I think I am asking a silly question,but plz help me.I am defining a function in global space and I want to use it in a class.but error is coming saying that this function is undeclared identifier.How to do that? <bhattacharjeesoft@gmail.com> ha scritto nel messaggio news:1186204361.724267.84940@x40g2000prg.googlegroups.com... > I think I am asking a silly question,but plz help me.I am defining a > function in global space and I want to use it in a class.but error is > coming saying that this function is undeclared identifier.How to do > that? Could you please pos...

Attachment Array?
I am having to recreate lotus notes code into outlook code and am finding it very difficult since I don't know coding very well. It's taken me a long time just to get where I am using snippets from what I have found from others. I know that what I want to do is for each attachment in a single email, I want to save it off, "remember" the path and filename and create a description. Then I need to use the path and filename and the description later during the creation of an .ini file. However, I'm not doing it correctly. I thought I should be using the str...

How to insert Open File action in the IF Function?
Hello, How can I insert the Open File action (w/o any other messages) in the IF function - =IF (A1=A2,"C:\TRUE.WAV","C:\FALSE.WAV") or smth like that. I don't know how to write a Macros at all, so if anyone willing to write it for me I'll be grateful, though explanations will be required. My global problem is that I get some data from the web (the links are refreshing automatically) and I wanna do some kind of check - if the data is useful for me than a sound will be played to inform me, if not - silence. http://support.microsoft.com/default.aspx?scid=kb;en-u...

Error "undefined function 'left' in expression"
Hi everybody, Has there anyone got a solution to my problem: - recently got Windows Vista - since then, queries with the string function left (e.g. left("member",2) equals "me") don't work Further details: - Windows Vista Home Premium - Regional settings: Dutch - Office (Access) version 2003 SP2 Any help will be greatly appreciated. Thkx. M.S.Huizenga Reference problem. Go to the following web page for information on checking references: http://www.mvps.org/access/bugs/bugs0001.htm -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith ...

Use of "OnSubmit"
I would like to execute a function when the user clicks on the "SUBMIT" button in a PHP/HTML form. I think I should use "OnSubmit" event. How to use OnSubmit in a PHP form? Is following syntax correct? <form method="post" action="G_Training.php" name="G-User-Details" style="border: thin double #FF0000" title="User Details" id="New Form" onsubmit="checkFormBeforeSubmit"> Submitted via EggHeadCafe - Software Developer Portal of Choice A Down and Dirty FileSystem Object Page Counter http://...

Undo function suddenly lost ?? #2
Hi NG. I have suddenly lost the abillty to "Undo" actions in my MicroSoft Visio Professional 2002. Are there any typical reasons ? I have checked that undo levels > 0, it's 20. -- mvh/rg. Christian ...

Using Excel spreadsheet online
I have built a spreadsheet that allows me to track inventory and revenue for products that i have a team of people selling. I sthere any way that I can post this spreadsheet online so that my salespeople can update it from a browser? I know a bit about Excel but I don't know diddly about what might be needed on the server end or how it would save the data. Any sugestions? "Monkeysuit Man" <monkeysuitman@monkeysuit.com> wrote in message news:18m7mvsiqkonfcmlpfl125rc5s3h0gfah8@4ax.com... > I have built a spreadsheet that allows me to track inventory and > revenue fo...

how use dlookup to update table
hi everybody I come from china. Firstly,I'm sorry. My english is not good. I have a question about dlookup I have two tables. One table (table1)only have two fields "file name" and "file ID". Another table(table2) also include field "file ID" and "file name". table1 have many records. I want to accord record from table1 to update table2 when table2.file name=table1.file name. I design a form to input table2, there is a combobox in the form that I hope its list from table11.file name,when I choice a file name another textbox value will ...

Zip files using makeDirZip.exe
Hi All, I need to zip subfolders which resides under the parent folders which are 201003,201004,201005(for each month). Under the parent folder.example 201003->Titles->then many sub folders for each day of the month,I need to use MakeDirZip.exe to only zip sub folders for each day under the root folder(201003) which is 2 months old from this month. Please shed some light on it. I am trying to use, C:\test>makeDirZip.exe i=\\<filer>\c$\cdc dubug='yes' purge='no' which is zipping every single date folder no matter whether this month or 2 months ol...

Using Outlook BCM on a server
Can I use Outlook BCM in a server (the contact database residing on server) and access that information, add to it seamlessly, etc. Also, can I see all notes (opportunity notes, time/date stamping) in a single screen? I think I posted it in a wrong place. "Yuri" wrote: > Can I use Outlook BCM in a server (the contact database residing on server) > and > access that information, add to it seamlessly, etc. > Also, can I see all notes (opportunity notes, time/date stamping) in a > single screen? "Yuri" <Yuri@discussions.microsoft....

Monthly Reports use closed accounts
I just upgraded to Money Plus Premium and was trying various things to make sure it all worked. I can set up most reports to include the Accounts and Investments I want but the Monthly Reports won't allow me to do that. When I run them that report includes accounts I have closed years ago and investments I have sold. The result is that the section that shows my best and worst investments for the month is pretty useless because it lists mostly things I have no interest in. In fact I suppost all the other information in the monthly report is equally useless since it is working on the ...

functions in SmartList Builder
Hello: Does anyone have any documentation on formulas and functions in SmartList Builder? I have used SmartLsit Builder for several clients. But, I am finding no documentation on PartnerSource on the functions and formula capabilities for calculated fields. Thanks! childofthe1980s childothe1980s- We have a product called SmartList Analyzer that allows you to perform several different calculations on any data in SmartList. Functions include Sum, Average, Mode, Standard Deviation, and more. Please contact support@KlenzmanConsulting.com Regards, Brenner "childothe1980s&...

Average function for more than 30 items
How can I use Average function to calculate average of more than 30 items? becasue Average function do not accept 31st item & gives an error Or Is there any other alternative. Thank you, Murtaza Don't use =AVERAGE(A1,A2,A3,A4,...,A100) ... won't work Use: =AVERAGE(A1:A100) Mangesh "Murtaza" <NoEmail@NoEmail> wrote in message news:OXQp4E7zFHA.904@tk2msftngp13.phx.gbl... > How can I use Average function to calculate average of more than 30 items? > becasue Average function do not accept 31st item & gives an error > > Or Is there any other a...