#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Thank you "jhweaver".
wConti |
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 |
Mysterious "Subscript out of range" error | rnstewart | Excel | 4 | 12-29-2005 01:04 PM |