SUMIF and SUMIFS Error

It seems that I have to concatenate the criteria arguments in the
SUMIF and SUMIFS functions since I have moved to Excel 2007.  For
example, ">D$4" must become ">"&D$4 in the Excel 2007 version.  Is
this a change in syntax or a bug that needs to be addressed.  Any
thoughts...

Jim
0
clovisjim (3)
9/15/2010 1:43:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
610 Views

Similar Articles

[PageSpeed] 32

I think you're mis-remembering.  That was the syntax required in xl2003 (as far 
as =sumif() has been alive).

Well, unless you really wanted to use the string D$4 (not the cell with address 
$D$4).



On 09/15/2010 08:43, Jim wrote:
> It seems that I have to concatenate the criteria arguments in the
> SUMIF and SUMIFS functions since I have moved to Excel 2007.  For
> example, ">D$4" must become ">"&D$4 in the Excel 2007 version.  Is
> this a change in syntax or a bug that needs to be addressed.  Any
> thoughts...
>
> Jim

-- 
Dave Peterson
0
petersod1 (224)
9/15/2010 2:48:08 PM
Even needing to concatenate the ">" sign and the cell reference?  I am
not sure that I ever remember needing this syntax.

Here's another...conditional formatting has been "glitching" around a
lot, as well.  Have you noticed anything weird with this?  I have
tried the conditional formatting provided in the program and created
it on my own with the cell.  For example:

=if("D4="Years",E4/F4,text(E4/F4,"0.0%"))

This formula works most of the time, but just stops working in some
instances until you edit the formula, altering nothing, and
recalculate. Same with the conditional formatting functionality built
into excel.  (Note:  I have already verified uniform formatting of the
source date.)
0
clovisjim (3)
9/16/2010 2:01:09 PM
Yep to the concatenation question.

There are syntax errors in the formula you posted.  So I'm hesitant to guess 
what the problem could be.

But...

I've never seen this but there have been some posts where some formulas wouldn't 
recalculate (with excel in automatic mode and the cells that the formula uses 
changes).

One suggestion that seems to work at least temporarily is to:
Select all the cells
Edit|Replace
what:  =  (equal sign)
with:  =
replace all

The change forces excel to recalculate each formula and seems to wake up its 
calculation engine.

I'm not sure what could be going wrong with your conditional formatting, though. 
  You didn't post the rules you're using.  But maybe the edit|replace technique 
will fix it...

On 09/16/2010 09:01, Jim wrote:
> Even needing to concatenate the ">" sign and the cell reference?  I am
> not sure that I ever remember needing this syntax.
>
> Here's another...conditional formatting has been "glitching" around a
> lot, as well.  Have you noticed anything weird with this?  I have
> tried the conditional formatting provided in the program and created
> it on my own with the cell.  For example:
>
> =if("D4="Years",E4/F4,text(E4/F4,"0.0%"))
>
> This formula works most of the time, but just stops working in some
> instances until you edit the formula, altering nothing, and
> recalculate. Same with the conditional formatting functionality built
> into excel.  (Note:  I have already verified uniform formatting of the
> source date.)

-- 
Dave Peterson
0
petersod1 (224)
9/16/2010 6:14:48 PM
Reply:

Similar Artilces:

error bars #2
Help! I am trying to put error bars on my data. I have a number of series in my chart (xy plot). One set has a number of points (series 1), then I plot the average of those points in a different color (series 2) on top of that data, but when I put the error bars on the series 2, they are masked by the data from series 1 even though the data point of series 2 is on top of the data from series 1. Does anyone know how I can pull the error bars to the front so they are with the associated data point? I have tried moving the order of the series, but the error bars always remain in th...

Error 2185 on Snapshot form
I have a bound form that has several unbound controls in the header that are used only to filter the records on the form. The detail section has only one bound field enabled, and its only function is to allow the user to double-click it to open a detail form filtered to the record on which the user double-clicked. I have AllowDeletions = False, AllowAdditions = False, and RecordsetType = Snapshot because this is not an input form, just a search form. Now, in all my filtering controls, I have code similar to this to drop down the control on the first key press: If Len(ThisControl.Text)...

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

Delete on Continuous form gives error 2046
Hi All, I have hunted the newsgroup and tried many different things, but I still can't seem to get this right... I have a main form for entering in order information, and a sub form for adding in the items ordered. The subform default view is set to 'Continuous Forms', Allow Additions/Deletions/Edits are all true. the data entry property is false if that makes any difference. I have put a button (btnRemoveItem) into the details section of this subform - and have tried to write the code to have the current record deleted when the button is pressed. The code I used that gives th...

sendobject error 2295
I have a small app that distributes notification emails to different users based on 2 criteria selected by the user. I select the receipients based on the input values they indicate. The distribution lists are static strings from another file that I use... This approach has been working well with single quote enclosed email address separated with a trailing ; i.e. 'a user1'; 'a user2'; I assign the appropriate string(s) to a local variable that I use as the sendobject TO and CC parameters. I have confirmed every email address used in the strings are valid. I now rec...

Merge Error in Contacts
We converted an Act Database putting most of the records into Leads and only qualified records into Contacts. I've been in the process of identifying and merging duplicates. My latest test stripped all EMail addresses out of Leads and Contacts, sorting them and identifying duplicates. I can merge a Lead with a Lead I can merge a Contact with a Contact. Here's my problem: If I convert a Lead to a Contact and then try to Merge the two records I get an error followed by the following Script Error Line: 291 Char: 1 Error: 'crmMergeForm' is undefined Code: 0 ...

Error 2024
There is an older answer in the "Archives" here, but that didn't work for... F, 3/7/08 - Error 2024 - "The report snapshot was not created because you don't have enough free disk space for temporary work files." The Code is...DoCmd.OutputTo acOutputReport, "BudgetSheet", _ "Snapshot Format", "C:\BobDev\S" & Format(Now(), "mm/dd/yy") & ".snp", False TIA - Bob Hi Bob, This part of your expression, which you can test in the Immediate Window, results in illegal characters (forward slash) in your output...

error in sum formula result
I'm no Poweruser of Excel, just an occasional user with a little experience, but I have come across a strange behavior, and wonder if you have seen this before, and what might be causing it. I recently did a new spreadsheet to track some costs, and constructed a simple formula to extract a sum from three cells. The three cells each contained currency numbers, lets say 235.45, 100.44 and 95.51. The formula I used was =SUM(F13.F15), and the result was off by -.01. In other words the last three digits of each number, 5,4 and 1 should have yielded a 0, but in fact returned a 9. I chec...

error 16003, can't receive emails
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop Hi there. I can send emails fine but when i try to receive i am getting error message 16003 and "an unknown error has occurred in Entourage." Any ideas? Many thanks! ...

Error when publishing
When i publish, my website is showng the directory listing, not the home page. How can i correct this? thanks, -- Suki Arts by having an; index.htm or index.html or default.htm or default.html page in the root folder...which one of the four will depend on what your server is set up for. what is the file name of your "home" page? -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "Suki arts" <Sukiarts@discussions.microsoft.com> wrote in message news:73E49EC8-3B22-4182-99AB-F56B50CDCB16@microsoft.com... > When i pu...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

Lost mouse click after displaying error dialog
I am having a problem with a modification I am doing to an old piece of code. It is using Visual C++ 5.0. Basically I have added some code to check the range of a typed in value. If the value is out of range I am using an AfxMessageBox to display an error dialog. This is all triggered in the ON_EN_KILLFOCUS handler for the entry field. The problem I am having is that after clicking the Ok button on the error dialog, the next mouse click is 'lost'. Also the mouse cursor shows as an input cursor anywhere over the application window until I do the 'extra' click, then it goes ...

trigger odbc error
All, We have been experiencing issues where users are deleting records from inventory tables and making administration a real task. I have created a trigger to run and email me what has been deleted from one of the tables. However when I test the trigger it gives me an error stating that the DELETED table is not a valid object. Below is the trigger I created and following that is the error I get. CREATE TRIGGER dbo.IV00104_delete ON dbo.IV00104 FOR DELETE AS exec master..xp_sendmail 'neil.vidt@dynavoxtech.com', @query = 'SELECT ITEMNMBR, CMPTITNM FROM DELETED', ...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Error PLEASE HELP!!!
I'm trying to syudi Ansi C in this program like suggested by my University Book, #include "stdafx.h" #include <stdio.h> #include <string.h> #include <stdlib.h> #define MAXLINES 5000 /* max #lines to be sorted */ char *lineptr[MAXLINES]; /* pointers to text lines */ int readlines(char *lineptr[], int nlines); void writelines(char *lineptr[], int nlines); void qsort(void *lineptr[], int left, int right, int (*comp)(void *, void *)); int numcmp(char *, char *); int StrCmp(char *, char *); /* sort input lin...

out box error
i get this error on this accouts The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Live Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail address was 'dshipman@shipent.net'. Subject 'ttest shipent.net' Server Error: 550 Server Response: 550 not local host ...

Error: The ordinal 6880 could not be located in the DLL MFC42.DLL
I know - this is an old problem but still, I can't overcome it: A year ago I have stared receiving the above application error when I navigate to folders containing AVI files. Dismissing the dialog is followed by an apparent Windows Explorer crash and restart. ================================ The solutions I found in several sites are as follows: The WinXP file version 6.02.4131.0 *has* the ordinal 6880. Therefore, you've installed poor software installing an older version of that system file. If you're lucky, that copy was installed in a program folder and can ...

Organization Settings Error
Seems like everyone is having a similar issue with CRM 3.0, no supprise then whats following... Problem: Error message to contact sysadmin when selecting either System Settings or Fiscal Year Settings. (you can access Auto Numbering) Tried: Changed expiry on server to immediately from 3 days. Rebooted both server and client, with no fix. Deleted all cached Internet files, with no fix. Can anyone help ?? (my only other thought is to wait till Monday and see if anything hanging in the system expires (3 days)). Any help greatly appreciated. I dont know if this helps? You may have...

Error Code :0x80040605
Outlook asked me to upgrade the Connector when i choose to repair, nothing happened except that my email account could not sign in and when i choose remove the outlook connector automatically removed. Troubleshooting Outlook Hotmail Connector - http://www.officeforlawyers.com/outlook/tsol.htm#hm -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Karen" <Karen@discussions.microsoft.com> wr...

Installation error
Hi there, Does any have experianced problem with instaling service pack 4 on win200 prof. When I tried to install it I got a message outlook express\wabmig.exe is open or in use by another application. I can resoved this problem We are running ehchange server 5.5 for only internal emails and server NT4. Please help ! ...

Rules and Alerts gives me Out of memory or system resources error
Outlook 2003 Every time I go to "Tools" / "Rules and Alerts" I get the following error: Microsoft Office Outlook: Out of memory or system resources. Close some windows or programs and try again. Everything else is working perfectly fine, it's just this one function. thanks I am getting the same error from any PC that has Outlook 2003 installed! This is really annoying. Anyone with similar problem? gougler wrote: > Outlook 2003 > Every time I go to "Tools" / "Rules and Alerts" I get the following > error: > > Microsoft Office Out...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

uxtheme.dll Error with VS Tool Add-In
I deployed a GP add-in with Visual Studio tools. Works beautifully on an XP machine, but when I installed it on a Win2k machine it throws an exception: System.DllNotFoundException: Unable to load DLL 'uxtheme': The specified procedure could not be found. (Exception from HRESULT: 0x8007007F) at Microsoft.Dexterity.Shell.UI.LibWrapper.IsThemeActive() at Microsoft.Dexterity.Shell.UI.ColorHelper.AreThemesEnabled() at Microsoft.Dexterity.Shell.UI.DexUIForm.OnPaint(PaintEventArgs e) at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer, Boolean...

Error message
Hi If I use this formula =O14+L14+I14+F14 I get an error message. If I try this =IF(ISERROR(O14+L14+I14+F14),"",O14+L14+I14+F14) nothing shows even though cell O14 has the number (45). Any help appreciated. Karen My guess would be that one or more of the cells you are adding is text. Jeff "Kazza" <Karen_Thompson90@hotmail.com> wrote in message news:1149777452.690929.289340@j55g2000cwa.googlegroups.com... > Hi > If I use this formula =O14+L14+I14+F14 I get an error message. > If I try this =IF(ISERROR(O14+L14+I14+F14),"",O14+L14+I14+F14) nothin...

mfc error
Microsoft is reporting an mfc error and I want to know how to delete the problem ...