# excel SUMIF formula

carmella 12:39 13 Aug 03
Locked

what am i doing wrong?
I have an excel spreadsheet with columns A (names) and B (values), 599 rows.
I need to calculate subtotals for colum B. So for all names that match in column A, I need to sum the values in column B.
This is the formula that I inserted in cell B601:
=SUMIF(\$A\$2:\$A\$599,A601,\$A\$2:\$B\$599)
it only returns zeros. any ideas?
many thanks
R

Ben Avery 12:48 13 Aug 03

Criteria cell is specified as A601.

Have you got anything in it?

BA

Ben Avery 12:54 13 Aug 03

Lets say you have 10 rows in cells A1:A10

4 Cells contain the name "John"

3 Cells contain the name "Bill"

1 Cell contains the name "Leah"

2 Cells contain the name "Dave"

**********************************************

In cells B1:B10 you have values of 100 in each.

**********************************************

In cell B12 you type the following formula

=SUMIF(A1:A10,A12,B1:B10)

(similar to yours with the criteria cell being A12 right?)

**********************************************

To find out the valu of all the "John"'s you would type "John" into cell A12. This will give the value of 400 in cell B12

Does this help soplve your problem?

P.s. you don't need to use the \$ signs for this.

BA

VoG II 12:54 13 Aug 03

Assuming you're trying to sum values in B that match A601 in column A

=SUMIF(\$A\$2:\$A\$599,A601,\$B\$2:\$B\$599)

seedie 13:09 13 Aug 03

This looks like you need an array formula

=sumif(a1:a599="John",b1:b599)

You need to press ctrl shift enter for array formulae

There again I could entirely wrong

Simsy 13:27 13 Aug 03

is that you have a "A" instead of a "B" near the end...

=SUMIF(\$A\$2:\$A\$599,A601,\$A\$2:\$B\$599)

should be, I think,

=SUMIF(\$A\$2:\$A\$599,A601,\$B\$2:\$B\$599)

which is what VoG has put..... You've just made a typing error I suspect.

Regards,

Simsy

carmella 14:39 13 Aug 03

yes, finally understood this. I followed Ben's suggestion, and also re-typed my previous typing error - works fine now!
thanks v. much
C

Ben Avery 14:51 13 Aug 03

for the record, could you enlighten me on the difference between the following:

=SUMIF(A2:A599,A601,B2:B599)

and

=SUMIF(\$A\$2:\$A\$599,A601,\$B\$2:\$B\$599)

What are the \$ signs used for as both seem to work identically?

BA

VoG II 14:55 13 Aug 03

In this case they will work identically.

The \$s make the cell references absolute.

Let's say if in B5 you have the formula =A5*A1

and you copied it down you would end up in B6 with =A6*A2

If you want always to refer to A1 (in this case) you would use =A5*A\$1 ro that when copied down to B6 would be =A6*A\$1.

Ben Avery 14:58 13 Aug 03

Really? That could prove most useful knowledge!

Thank you!

So how did you come to be such a dab-hand at excel? Are you some kind of Microsoft Beta tester or something???!! :O)

BA

Chris the Ancient 14:58 13 Aug 03

See if I can beat VoG to it (though I doubt it!).

The dollar signs 'lock' a cell reference to make it 'absolute'. This means if you copy a formula from one cell to another, any absolute cell reference will not be changed.

Normally when you copy a formula from one cell to another, cell references automatically change to reflect that move. That's called 'relative' referencing.

In reality, the \$ sign can be used to make a row - or a column - absolute, and any variation in between.

HTH a bit

Chris

This thread is now locked and can not be replied to.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…