I have a list of names in the first column with corresponding numbers across
subesquent columns:

A B C D
NAME 1 1 5 7
NAME 2 8 2 9
NAME 3 6 4 10

What I want is a sumif formula on a separate sheet to total columns b thru d
for each person based on the name in column a. This is the forumla I have
(the name of the sheet is Jan 8 - 20):

=SUMIF('Jan 8 - 20'!$A$5:$A$513,A1,'Jan 8 - 20'!$B$1:$D$3)

The problem is that the formula is only adding the numbers in column b and
none of the rest of the columns. I'm not sure what I'm doing wrong here.
Any suggestions?

You could use an array formula instead, like:

=SUM(IF('Jan 8 - 20'!$A$5:$A$513=A1,'Jan 8 - 20'!$B$1:$D$3))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brodiemac wrote:

I have a list of names in the first column with corresponding numbers across
subesquent columns:

A B C D
NAME 1 1 5 7
NAME 2 8 2 9
NAME 3 6 4 10

What I want is a sumif formula on a separate sheet to total columns b thru d
for each person based on the name in column a. This is the forumla I have
(the name of the sheet is Jan 8 - 20):

=SUMIF('Jan 8 - 20'!$A$5:$A$513,A1,'Jan 8 - 20'!$B$1:$D$3)

The problem is that the formula is only adding the numbers in column b and
none of the rest of the columns. I'm not sure what I'm doing wrong here.
Any suggestions?

--

Dave Peterson

try
=SUMPRODUCT(($A$2:$A$22=A2)*$B$2:$D$22)

--
Don Guillett
SalesAid Software

"brodiemac" wrote in message
...
I have a list of names in the first column with corresponding numbers
across
subesquent columns:

A B C D
NAME 1 1 5 7
NAME 2 8 2 9
NAME 3 6 4 10

What I want is a sumif formula on a separate sheet to total columns b thru
d
for each person based on the name in column a. This is the forumla I have
(the name of the sheet is Jan 8 - 20):

=SUMIF('Jan 8 - 20'!$A$5:$A$513,A1,'Jan 8 - 20'!$B$1:$D$3)

The problem is that the formula is only adding the numbers in column b and
none of the rest of the columns. I'm not sure what I'm doing wrong here.
Any suggestions?

Try:

=SUMPRODUCT(--('Jan 8 - 20'!$A$5:$A$513=A1)*('Jan 8 - 20'!$B$5:$D$513))

"brodiemac" wrote:

I have a list of names in the first column with corresponding numbers across
subesquent columns:

A B C D
NAME 1 1 5 7
NAME 2 8 2 9
NAME 3 6 4 10

What I want is a sumif formula on a separate sheet to total columns b thru d
for each person based on the name in column a. This is the forumla I have
(the name of the sheet is Jan 8 - 20):

=SUMIF('Jan 8 - 20'!$A$5:$A$513,A1,'Jan 8 - 20'!$B$1:$D$3)

The problem is that the formula is only adding the numbers in column b and
none of the rest of the columns. I'm not sure what I'm doing wrong here.
Any suggestions?