View Single Post
Old 09-06-2007, 06:13 AM
jhweaver jhweaver is offline
Join Date: Jan 2007
Posts: 6
Default SUMIF


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:

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;


"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;


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