Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2007, 10:25 AM
contiw contiw is offline
Novice
Range names and length stuff. Please help-a-noob.
 
Join Date: Aug 2007
Posts: 2
contiw
Default Range names and length stuff. Please help-a-noob.

=SUMIFS(E9:E1494,C9:C1494,1)
This formula works ok.
If I rename range E9:E1494 as "Col_1" and change the formula to


=SUMIFS(Col_1,C9:C1494,1)
I get #VALUE error.
The range is formatted as number.

Also ...

If I delete some cells in a range, referring formulae are modified accordingly, but if I add cells in the range the formulae do not recognize the change

Thanks for helping.
Reply With Quote
  #2  
Old 09-06-2007, 06:13 AM
jhweaver jhweaver is offline
Novice
 
Join Date: Jan 2007
Posts: 6
jhweaver
Default SUMIF

Hi,

The syntax for SUMIF is;
=SUMIF('1st cell in range to check':'last cell in range to check','value to check for','1st cell in range to sum':'last cell in range to sum').

for example:

A B
1 Fred 5
2 Mary 4
3 John 8
4 Mary 7

We want to check the names in Col A for (say) Mary and sum the values against Mary in Col B. The formulae will be;

=SUMIF(A1:A4,"Mary",B1:B4)

"Mary" could be a Cell Ref containing the Value, ie. C1

The problem with the above formulae is that when you start adding or deleting rows/columns Excel does not always update the formulaes correctly.

A better option (assuming cols A and B are exclusive and don't have any other data below it) is to sum the columns. For example;

=SUMIF(A:A,C1,B:B)

Assuming C1 holds the value of "Mary"

Using it this was means you can happily add or delete rows and the formulae will still give the correct answer (11 in this case) as it is counting the entire column and therefore is unaffected by rows being removed or added.
Reply With Quote
  #3  
Old 09-06-2007, 07:59 AM
contiw contiw is offline
Novice
Range names and length stuff. Please help-a-noob.
 
Join Date: Aug 2007
Posts: 2
contiw
Default

Thank you "jhweaver".
wConti
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to set the same length for different lines myword29 Word 0 09-28-2007 08:12 PM
A Noob Question Techknology Outlook 0 11-30-2006 07:47 AM
Two Different Names in the Same File kathy carson Outlook 0 02-14-2006 01:02 PM
Range names and length stuff. Please help-a-noob. Mysterious "Subscript out of range" error rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:58 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft