In this tutorial, you will find a handful of advanced formula examples that demonstrate how to use Excel's VLOOKUP and SUM or SUMIF functions to look up and sum values based on one or several criteria. Continue reading
Comments on: How to use VLOOKUP with SUM or SUMIF functions in Excel
by Svetlana Cheusheva, updated on
Comments page 7. Total comments: 441
I have 20 codes in sheet 1 and need to sum up the total sum given on each codes from sheet 2. Can you help me come up the formula?
Ex.
Sheet 1:
CODE - TOTAL QTY
ABC1 - 2212
ABC2 - 271
ABC3 - 486
ABC4 - 111
Sheet 2:
Store1:
Code - Size Total Qty
ABC1 - 50in 500
ABC1 - 20in 604
ABC1 - 45in 332
ABC2- 64in 122
ABC2- 220in 123
ABC3- 123in 443
ABC4- 14in 443
ABC4- 122in 111
Store2:
Code - Size Total Qty
ABC1 - 50in 222
ABC1 - 20in 223
ABC1 - 45in 331
ABC2- 64in 15
ABC2- 220in 11
ABC3- 123in 43
Appreciate your advise for this formula
Hi Svetlana,
Could you please help me with my table. I have the below database with more than 680 rows of information. There are positive and negative values in the column, the total sum of which is zero. How do I build the formula to exclude all these matching values from the column?
Many thanks in advance for your help!
Kind regards
Hi, Viorica,
what do you understand by "matching values"? Those that are completely identical? Or maybe those of the same number but with different signs (positives/negatives)?
For now we can assume that if you have 0 as a result, it means that every positive value has a corresponding negative value. If you need to sum only positive ones, then:
=SUMIF(A1:J12, ">0")
For negatives only
=SUMIF(A1:J12, "<0")
If this won't solve your task, let us know more details.
Hello sir,
Could you help me I want to make my table. When I use SUMIF it just Sum for whole column only. I want to sum at concern code no. row and column. How can I sum code 100 in total by using SUMif or vlookup or hlookup or arry.
Code Job 1 job 2 job 3
100 $30
101 $50
103 $15
101 20
200 $10
103 $11
Best regards,
Ying Kham
Hello Svetlana,
I couldn't follow ur guide, could you pls help me guide to write one formula, forexample.
All my data in sheet2 and on sheet1 i have summary table that for specific ID of person i want summ all data from sheet2. how to do?
Hello,
I have a spreadsheet with different tabs. I want to figure out how to count how many sales of a particular product (9 products), was sold by each salesperson (150 sales people. In addition I want to figure out how much $ those sales produced by each salesperson. Theis data is housed on a tab called March 3 Data.
Please help.
Thank you
D
Hi.
I have an excel file with 30 tabs each with a range of financial data covering a 1yr period. I would like to, on a separate tab within the same file, create a formula which searches for the MAX and MIN value for each of those ranges of data, referencing (I suppose) two date cells which represent the range I am looking to search within. In other words, today, I would like to search for the MAX value during the last month on all of those tabs. And then, in a week, I would like to be able to adjust the search to again find the updated MAX value for the last month (ie, with the last data point being one week later). Can I do this? If so, how? Thanks, Kim
Hi,
Have a question regarding sum + vlookup. In your example, you made the col_index_num an array by using {2,3,4,5...}. Is there a way to make this dynamic by referencing a value in a cell? For example, I input a value into cell V1=2 and I would like it to sum over 12 columns i.e. V1 to V1+12. The array wont let me do {V1:V1+12}. Any help is appreciated.
Thanks
Praveen
across rows in the same column
I am trying to something very simple and cannot figure out how to do it by reading your examples. Let me explain. I have two spreadsheets where I am looking up the value in one an comparing it to the value in the other and where there is a match, returning the value in the corresponding column number =iferror(vlookup(B6,DetailDate!$D$59292:$BQ$59291,31,),0)
This returns the first value of the row that matches. The problem is that there are multiple rows that match and I need to sum them together before returning the value. What would the formula be?
I tried to create an array for the one column (top to bottom) but it returns a 0 value.
I need to add the values in the same column but in multiple rows.
The example sum(vlookup)you provided adds values across columns. I need to add them across rows.
Beautifully written and clear guide, thanks Svetlana!
I need some assistance with sumarising some detail information from the following sheet:
Category; Trx Date; VOLUME
In this sheet I have multiple categories that gets repeated on numerous Trx Dates.
I need to sumarise this information into a sheet that looks like this:
Trx Date 1 Trx Date2 Trx Date3
Category 1
Category 2
Category 3
Can someone please help me with the formula to achieve this.
Thanks
I HAVE 2 SPREADSHEET AND I WANT INVOICE NO. FROM 2ND SHEET IN 1ST SHEET BUT CONDITION IS THAT ALL PRODUCT CODE DETAILS MATCH WITH 2ND SHEET PRODUCT CODE PLEASE HELP ME WHAT FORMULA I USE.
I have a spreadsheet that adds up daily usage of a material and subracts from the balance. It also adds that incoming inventory back in.
Example =(K3781+I3782)-(J3782)
Currently I manully enter the projected usage into this cell, this then gives me the projected balance.
Example =(K3781+I3782)-(J3782)-940-568
I would like to be able to export the information into another tab and excel calculate for me. The problem that I have is that multiple usage on the same date. I dont want to sum because I would like to associate the usage to a partiacular number. I can also have the same number on the same date that drives usage. Would it be possible submit an eample of what I am trying to explain?
hi,
am looking for a formula where I can average 7 values from 8 by taking the first 5 and choosing the best 2 from the remaining 3 values.
hi
I am getting close to the formula
i want to enter a serial number in Column B1:B23 page 1
then it matches serial number page 2 column A:1:a23
than data(written and date) from page 2 column b,c shows up in page 1 column c,d
i cant get it without error and both columns showing up
Hi, So I have a column A of name of people and their spending in a year in Column. I need to calculate how much each person spent by looking up the name so that if new entries are added the SUM automatically gets updated. Is their a way?
So close. The solution I used was SUMIFS which is similar but uses two columns to grab info from a third.
Thanks.
Does the Vlookup and Sum work with repeated values? for example if you had 2 columns with the name apples and want to sum both of the totals for both of the apple columns into one combined total. I am trying to generate a spreadsheet that will wind repeating cost codes and add the repeating cost codes for me. Please let me know how i can go about this!
Hi
As part of a larger problem I want to calculate the sum of top "n" values, where n will be user defined.
The sum will be calculated for an array,which has been calculated using several conditions using array formulas.
Eg: After all the conditional array formula, say I am getting an array as {0;23;45;0;0;10;1}
This array is dynamic based on the conditions provided. So, is there a way to find the sum of say first 3 or first 4 non zero numbers from this arrray ?
Thanks in advance
Thanks for the great article and I'm hope I'm not repeating a previous question...
When writing in the numbers of your array {2, 3, 4, 5, 6} and so on, is there a way to list an entire range of values at once? Say if you want to sum indexes 2 to 100. I noticed you mentioned the index function if I'm trying to sum over hundreds of columns for a given criteria. Should I not be using this array vlookup option for this case?
Happy to investigate another link if the answer is already out there. Pass it my way.
Thanks again for your clear explanations!
Ed
Hi all
Could you please tell me how in range of say some amounts, how to arrive at a particular sum amount. Suppose I have 50 Invoice amount due from a customer. I received a payment which includes 10-20 invoices. how can i know which amounts add to that particular cheque amount. Could you please give me the formula. Thanks
how to use sum and v lookup farmula from multiple rows
BANK 301370
BANK 736310
BANK 1473600
CONST. 100
CONST. 200
CONST. 200
CONST. 200
CONST. 200
CONST. 218
CONST. 300
CONST. 29000
CONST. 50000
CONST. 68800
FREIGHT 50
FREIGHT 200
FREIGHT 450
GENERAL 50
GENERAL 200
GENERAL 220
GENERAL 250
GENERAL 300
GENERAL 380
i want sprate total like summery
Hi, I hope someone can help. I would like to add up 2 sets of 2 columns with multiple codes for certain results.
For example/
Column A (company) consists of:#1 and #0
Column B (company) consists of:for #1 - A,B,C,D,Z
for #0 - F,G,H,I,J,K,L,M
Column D (contact) consists of: #0, #1 and #2
Column E (contact) consists of: for #0 - A, B
for #1 - C, D, E, F, G, J
for #2 - C,D,E,F,G,I,K,L,Z
Thanks in advance
Hello Dear,
i know sumif formula. But i don't no vlookup sum formula that means ID Value Total. for Example,
Sheet1,
ID Value
A1 100
B1 200
C1 300
A1 500
B1 300
C1 400
and i needed ID wise sum A1=100+500=600.
B1=200+300=500.
Please help me.
I have a spreadsheet for monthly/yearly overtime worked per job position at my company. People can also be charged time and a half or time and three quarters, depending on their schedule.
I need a way to search column b for job position and column c for type of schedule and if those equal PositionA ScheduleA to add column f's numbers all together in Cell Y. If PositionA Schedule B, add them together in Cell Z.
Is that too complicated for Excel?
Hello,
Is it possible to sum a row of numbers only if those numbers are from a formula? I am using a forecasting spreadsheet and any future figures are input manually by the budget managers as a 'guess'. However the actual spends for each month up until that point are found by using a VLOOKUP formula.
Basically I want to only sum the numbers if they are from the VLOOKUP formula.
Thank you for any help you can provide.
Hi All
I hope you are all well.
I don't know if this is going to be a bit of a stupid question, and I hope I make myself as understandable as possible. :)
Firstly, I am using Excel 2016 on Windows 10.
This is the scenario:
Book 1
- I have one Excel workbook with individual invoices for multiple customers. For example, the most important info:
Column A - Customer name (There are sometimes more than a few invoices for one customer)
Column B - Date
Column C - Invoice amount
Book 2
- I have another Excel workbook with the same as above, but they are credits relating to those invoices/customers:
Column A - Customer name (Same as above - more than a few lines for one customer, many different customers)
Column B - Date
Column C - Credit amount
Book 3
- The other Excel workbook is a consolidation of the above. I need to consolidate the above by customer, by month (working on November 2015 to October 2016 MTD). I considered doing a VLOOKUP by customer, but I don't know how to do a SUM VLOOKUP to add together all invoices for one customer in a date range from Book 1 onto Book 3, then the same for the credits from Book 2 to Book 3, so that I can calculate the difference.
I hope someone can assist me with this, and that I have made the question quite clear. :)
Thanks in advance!
How can I remove N/A
Sheet 1 Sheet2 Sheet 3 Sheet 4
As=(Vloopup)+(Vlookup)+(Vlookup)+(Vloopup) from different sheet, one sheet have no value(Like Sheet 4), but other sheet have value(Sheet 1,2,3) its showing N/A. If I use IF(ISERROR(Vlookup), then showing 0. But its not true.How can i solve this problem
Urgent
Hi,
I am trying to combine a SUMIFS and a VLOOKUP. My data set is big, and I run into problems when a name that used to be in the data is deleted from the data. That is why I want the forumla to lookup at the ID number of the person in my spreadsheet first, and then match it with their value in the data tab. Some people are in there twice, same ID but different two different values. I was a forumala that will match to the first and then match to the second.
Can someone help with this?
i have tried this with sumif(vlook up) but i dont get the esult and i cudnt find my error in formula
Hi
I am trying to do a sumif. The criteria are on two separate tabs. I want to sum if the attribute is "adjustable", but this attribute is on a separate tab so I have to do a vlookup to see if it is adjustable or not. Once something is adjustable, then it should be summed up. I've tried the following two formulae, and they don't seem to work:
=SUMIFS('LG dataset Subfund Method'!I$2:I$611,vlookup('LG dataset Subfund Method'!F2,'Attributes Matrix'!$A$2:$G$611,7,false),Adjustable)
and
=SUMIFS('LG dataset Subfund Method'!I$2:I$611,INDEX('Attributes Matrix'!$G$2:$G$611,MATCH('Attributes Matrix'!A2,'Attributes Matrix'!$A$2:$A$611,0)),Adjustable)
Basically I'm trying to create a logic that says sum up the cashflows on the LG tab only if the attribute attached to the plancode is Adjustable and this part is on the Attributes Matrix tab.
Hi.,,
May I Know How To Add Number with the Help of Vlook_Up..
For Ex:
Sharat
Fruits Numbers
Apple 190
Banana 111
Mango 503
Geeta
Mango 113
Banana 190
Apple 190
watermelon 190
Ram
Banana 128
Apple 147
watermelon 180
Mango 114
..
...
How i Can Add Like......
Fruits Total Items
Total Banana ?
Total Apple ?
Total Mango ?
Total watermelon ?
Hi, I have a worksheet with a matrix of names (6 columns of differing names in each rwo) and in another column I have either "Won" or "Lost".
I want to count the number of "Won"s for each person.
So I have a formula: =COUNTIFS(TMT!$F$2:$K$29,B4) which finds and counts all the times the person's name in B4 exists. This works OK.
But when I add another selection =COUNTIFS(TMT!$F$2:$N$29,B4,TMT!D:D,"=Won") I get an error "A value used in the formula is a wrong data type". However if I enter only the second selection on it's own I get no error.
Any ideas?
Dear Philip Morris just remove = of "=won" from =COUNTIFS(TMT!$F$2:$N$29,B4,TMT!D:D,"=Won")
Hi,
I have to find sum of sale qnty using vlookup
first sheet where purchase is
Code Qnty
NFSC 2
NFCC 5
NFDD 7
Other sheet where sale of the month is (date wise sale)
Code Qnty
NFSC 1
NFCC 1
NFDD 1
NFSC 1
NFDD 1
NFCC 1
Hi there,
Your tutorial is good but i'm struggling to adapt it to my situation and wondered if you could help?
I have a workbook for timesheets that contains 53 sheets, one for every week. Each sheet is formatted the same and shows the job number and job name in rows above one and other in the first column. These rows are then combined in the next column to show total hours worked, then the next columns represent the actual days of the week and hours worked per day.
I would like to have a summary table at the end of the workbook that tells me total hours worked in the year and total hours spent per job number. i've tried a variety of sum and look up combinations and nothing has worked yet.
any pointers you can make would be greatly appreciated.
Respected Able Bits Team;
Please give me merge tables wizard excel 2003 .xls format
Regards
Saurabh Sharma
Hi,
I have a fairly simple task that I just can't figure out a formula for it. I have a range of account # that I need to find and sum on one cell.
For example,
I need to sum account numbers 2001-2020. All accounts from 1000 to 3000 are listed in column B (in another tab within the same work book). Column C in that same tab has the account values that I need to sum.
I tried the SUM-LOOKUP combo but sorting in ascending order is not an option for me. What other formula can I use to get to my desired result?
I would really appreciate your help with this!
Hello Svetlana,
What you have shown is simply amazing and I sincerely appreciate the time and effort you have put. Thanks a lot!
I have a small query which I believe has no solution but would like your opinion on. So I am working on a case similar to SUMIF and VLOOKUP combination example above, but the difference is that my lookup array is spread across multiple worksheet and not just one main table. Any thoughts on how to resolve it? I was thinking using SUMIFS and VLOOKUP maybe, but not sure! :-(
Anky
How to calculate catagory wise cummilative value like
Mc. Qty
Duke120. 3
Duke 220. 4
Deke 330. 3
pulser 120. 5
Pulser 220. 7
Pulser 250. 8
Now what im want is cummilative value of duke and cummilative value of pulser different wise but on a same column.
when i create invoice ...then automatic stock credit or debit.,amount plus in customer account.,when enter part number then automatic fill part discription....i wish this formula
Good Day! What is the formula for the =Sum(Vlookup) For vertical addition of data?
Regards
Tlhogi
I have been struggling to fulfill my boss requirement but still unsuccessful. Maybe someone can help me with the quickest way.
I have a workfile with multiple sheets but each sheets uses the same format. Example:
Sheet 1.
Part Name: ABC
Type of Defects 1-May 2-May 3-May 4 May
Defects 1 10 100 50 60
Defects 2 12 50 20 30
Sheet 2 or 3 or 4 are the same as sheet 1 except
Part Names are different
I want to create a Summary page whereby I just need to key in the following criteria:
Part Name : XXXX ( just key in the part number )
Date Start : Select which start date
Date End : Select which end date
With this 2 criteria typed in, all the defects will automatically add up but still separated still into Defects 1 and Defects 2.
In other words, the left hand column will still have Defects 1 and Defects 2 and so on.
Can someone help me?
Hye,
I'm meeting trouble in doing sum of different vlookup values. I try explain my problem with an example given below:
| A |B |C |D |E |F
1| 02-01 |48 |22 |38 |54 |98
2| 03-01 |20 |105|111|50 |40
3| 03-02 |35 |40 |67 |66 |901
4| 03-03 |88 |50 |100|47 |200
In given above table I want to sum all values vertically in column F with reference to Column A but want to add only that values of which code in Column A start with 03.
Please guide me. You can mail reply on my mail ID if convenient. Thanks
i want lookup A3 value in range B3:B10 if cell value verify then result show with text and commas
one problem mostly i face when i update the advance of employee .the problem is . there are three column
column(A) column(B) column(C)
employee punch n. Advance
mohit 54 1500
mukesh 84 2500
rahul 90 2000
mohit 54 500
sabir 92 5000
is there any formula that add the amount that is double to a employee
by a formula in excel sheet when i prepare salary and adjust the advance .
Hi Svetlana,
I have a data in which first row contains date and other three rows contains their pageviews, visits. I need your help to merge date wise data in which i can the total visits pageviews and as well as visitors for all dates seperately this database contains 4700 rows.
Below are the example of data.
Looking forward for your reply.
Date Visitors Visits Page Views
1-Jan-08 110,318 143,414 165,477
1-Jan-08 111,056 144,372 166,583
1-Jan-08 112,961 146,850 169,442
1-Jan-08 110,977 144,271 166,466
1-Jan-08 110,561 143,730 165,842
1-Jan-08 105,094 136,622 157,641
1-Jan-08 112,742 146,565 169,113
1-Jan-08 108,948 141,632 163,422
1-Jan-08 110,211 143,274 165,316
1-Jan-08 107,731 140,050 161,596
2-Jan-08 107,917 140,292 161,876
2-Jan-08 109,908 142,880 164,862
2-Jan-08 105,705 137,417 158,558
2-Jan-08 106,791 138,829 160,187
Thanks,
Nandan
You can use Paviot Table. It will surely convenient for you.
Paviot Table can be added from Insert tab.
how can we solve it by only using vlookup....
Hi,
I have 12 columns and thousands of rows. On columns I have (Years-Months-Factories-a-b-c-d-e-f-volume-sales-price)..What I want to do is, I want to calculate total volume for each factory(4 factory) by months in each year..
Thx
hi,
I am using 2 sheets with daily sales in sheet1 and inventory in sheet2. I want to put sum formula in sheet2 so that it pick an item no and goes to sheet1, locates its occurrence and than calculate from values in corresponding columns. But the tricky part are, first, values are in 3 columns - sale(-), return(+) and stock delivery(+). Second, sale for same item will happen on multiple days, so all will be factored in and entered before the item in inventory list.
I have input an array formula with sum and vlookup in sheet 2. It gives value for only one column and one row.
Please help.
i need to calculate the average value for male and female students in class. but their title in different page and final mark in different page. need to do it using vlookup..