Help with Excel Sum Function

T

Tom

Hi All,
I need help with an Excel formula, I'm trying to create a formula that will
calculate the total for each client from the Jan workbook and put it on the
Summary workbook, matching by client name columns (Client). So for example
sum in col C3 in Summary all entries in Jan col C that match Alkeon by
matching the name in Summary A3 with Jan Col C giving a total of 1083.76 from
the data below.

Summary (Workbook)
(Cell)A C
1Client Jan
21798 Capital #N/A
3Alkeon 1083.76
4Alydar 823.90

Jan (Workbook)
(Cell)C G
1Client Cost
2Alkeon 541.88
3Alkeon 541.88
4Alydar 618.9
5Alydar 102.50
6Alydar 102.50

I've tried sum & vlookup but neither one quite worked (ex below), what's the
best function to accomplish this?
I've tried
=SUM(IF(Jan!$C$2:$C$194="Alydar",Jan!$G$2:$G$194,0))
and
=VLOOKUP(A3,Jan!C$2:G$999,5,FALSE)
but neither one quite worked, any suggestions?

Tom
 
S

smartin

Tom said:
Hi All,
I need help with an Excel formula, I'm trying to create a formula that will
calculate the total for each client from the Jan workbook and put it on the
Summary workbook, matching by client name columns (Client). So for example
sum in col C3 in Summary all entries in Jan col C that match Alkeon by
matching the name in Summary A3 with Jan Col C giving a total of 1083.76 from
the data below.

Summary (Workbook)
(Cell)A C
1Client Jan
21798 Capital #N/A
3Alkeon 1083.76
4Alydar 823.90

Jan (Workbook)
(Cell)C G
1Client Cost
2Alkeon 541.88
3Alkeon 541.88
4Alydar 618.9
5Alydar 102.50
6Alydar 102.50

I've tried sum & vlookup but neither one quite worked (ex below), what's the
best function to accomplish this?
I've tried
=SUM(IF(Jan!$C$2:$C$194="Alydar",Jan!$G$2:$G$194,0))
and
=VLOOKUP(A3,Jan!C$2:G$999,5,FALSE)
but neither one quite worked, any suggestions?

Tom

You were very close with
=SUM(IF(Jan!$C$2:$C$194="Alydar",Jan!$G$2:$G$194,0))

In fact, if you enter this as an array formula by pressing
Ctrl+Shift+Enter it should work.

However, in your case SUMIF is the easier solution and does not require
array-entry:
=SUMIF(Jan!$C$2:$C$194,"Alydar",Jan!$G$2:$G$194)

In either case, you can replace "Alydar" with a reference to make the
formula more generic. E.g. in Workbook cell C2:
=SUMIF(Jan!$C$2:$C$194,A2,Jan!$G$2:$G$194)

....and copy down as needed.
 
P

Per Jessen

Hi Tom

Look for the SUMIF function in help.

=SUMIF(Jan!A2:A6,A3,Jan!C2:C6)

Regards,
Per
 
T

Tom

thanks, the second sumif was what I was looking for, it looks like exactly
what I wanted.
tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top