# excel SUMIF formula

carmella 12:39 13 Aug 2003
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 2003

Criteria cell is specified as A601.

Have you got anything in it?

BA

Ben Avery 12:54 13 Aug 2003

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 2003

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 2003

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 2003

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 2003

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 2003

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 2003

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 2003

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 2003

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.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Best Android emulators for Mac

TV & Streaming : comment regarder Roland Garros ?