If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Excel Sum Function
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 |
#2
|
|||
|
|||
Help with Excel Sum Function
Tom wrote:
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. |
#3
|
|||
|
|||
Help with Excel Sum Function
try this
=SUMIF(Jan!$C$2:$C$194,"Alydar",Jan!$G$2:$G$194) "Tom" wrote: 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 |
#4
|
|||
|
|||
Help with Excel Sum Function
Hi Tom
Look for the SUMIF function in help. =SUMIF(Jan!A2:A6,A3,Jan!C2:C6) Regards, Per On 29 Sep., 01:48, Tom wrote: 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 |
#5
|
|||
|
|||
Help with Excel Sum Function
thanks, the second sumif was what I was looking for, it looks like exactly
what I wanted. tom "smartin" wrote: Tom wrote: 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. |
Thread Tools | |
Display Modes | |
|
|