Register FAQ Search Today's Posts Mark Forums Read

#1
08-01-2007, 10:25 AM
 contiw Novice Join Date: Aug 2007 Posts: 2

=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
09-06-2007, 06:13 AM
 jhweaver Novice Join Date: Jan 2007 Posts: 6
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
09-06-2007, 07:59 AM
 contiw Novice Join Date: Aug 2007 Posts: 2

Thank you "jhweaver".
wConti

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post myword29 Word 0 09-28-2007 08:12 PM Techknology Outlook 0 11-30-2006 07:47 AM kathy carson Outlook 0 02-14-2006 01:02 PM rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:13 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top