by 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.
Are you trying to create a summary file in Excel that will identify all instances of one particular value, and then sum other values that are associated with those instances? Or, do you need to find all values in an array that meet the condition you specify and then sum the related values from another worksheet? Or maybe you are faced with a more concrete challenge, like looking through a table of your company invoices, identifying all invoices of a particular vendor, and then summing all the invoice values?
The tasks may vary, but the essence is the same - you want to look up and sum values with one or several criteria in Excel. What kind of values? Any numeric values. What sort of criteria? Any : ) Starting from a number or reference to a cell containing the right value, and ending with logical operators and results returned by Excel formulas.
So, does Microsoft Excel have any functionality that can help with the above tasks? Of course, it does! You can work out a solution by combining Excel's VLOOKUP or LOOKUP with SUM or SUMIF functions. The formula examples that follow below will help you understand how these Excel functions work and how to apply them to real data.
Please note, these are advanced examples that imply you are familiar with the general principles and syntax of the VLOOKUP function. If not, the first part of our VLOOKUP tutorial for beginners is certainly worth your attention - Excel VLOOKUP syntax and general usages.
Excel VLOOKUP and SUM - find the sum of matching values
If you work with numerical data in Excel, quite often you have not just to extract associated values from another table but also sum numbers in several columns or rows. To do this, you can use a combination of the SUM and VLOOKUP functions as demonstrated below.
Source data:
Suppose, you have a product list with sales figures for several months, a column per each month. The source data is on the sheet named Monthly Sales:
Now, you want to make a summary table with the total sales for each product.
The solution is to use an array in the 3rd parameter (col_index_num) of the Excel VLOOKUP function. Here is a generic formula:
As you see, we use an array constant in the third argument to perform several lookups within the same VLOOKUP formula in order to get the sum of values in columns 2,3 and 4.
And now, let's adjust this combination of VLOOKUP and SUM functions for our data to find the total of sales in columns B - M in the above table:
=SUM(VLOOKUP(B2, 'Monthly sales'! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))
Important! Since you are building an array formula, be sure to hit Ctrl + Shift + Enter instead of a simple Enter keystroke when you finished typing. When you do this, Microsoft Excel encloses your formula in curly braces like this:
{=SUM(VLOOKUP(B2, 'Monthly sales'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}
If you press the Enter key as usual, only the first value in the array will get processed, which will produce incorrect results.
Tip. You may be curious why the formula displays [@Product] as the lookup value in the screenshot above. This is because I converted my data to table (Insert tab > Table). I find it very convenient to work with fully-functional Excel tables and their structured references. For example, when you type a formula into one cell, Excel automatically copies it across the entire column and in this way saves you a few precious seconds :)
As you see, using the VLOOKUP and SUM functions in Excel is easy. However, this is not the ideal solution, especially if you are working with big tables. The point is that using array formulas may adversely affect the workbook's performance since each value in the array makes a separate call of the VLOOKUP function. So, the more values you have in the array and the more array formulas you have in your workbook, the slower Excel works.
You can bypass this problem by using a combination of the INDEX and MATCH functions instead of SUM and VLOOKUP, and I will show you a few formula examples in the next article.
Download this VLOOKUP and SUM sample
How to perform other calculations with Excel VLOOKUP function
A moment ago we discussed an example of how you can extract values from several columns in the lookup table and calculate the sum of those values. In the same fashion, you can perform other mathematical calculations with the results returned by the VLOOKUP function. Here are a few formula examples:
| Operation | Formula example | Description |
|---|---|---|
| Calculate average | {=AVERAGE(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and calculates the average of values in columns B,C and D in the same row. |
| Find maximum value | {=MAX(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and finds the max value in columns B,C and D in the same row. |
| Find minimum value | {=MIN(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and finds the min value in columns B,C and D in the same row. |
| Calculate % of sum | {=0.3*SUM(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table', sums values in columns B,C and D in the same row, and then calculates 30% of the sum. |
Note. Since all of the above formulas are array formulas, remember to press Ctrl+Shift+Enter to enter them correctly in a cell.
If we add the above formulas to the 'Summary Sales' table from the previous example, the result will look similar to this:
Download this VLOOKUP calculations sample
LOOKUP AND SUM - look up in array and sum matching values
In case your lookup parameter is an array rather than a single value, the VLOOKUP function is of no avail because it cannot look up in data arrays. In this case, you can use Excel's LOOKUP function that is analogues to VLOOKUP but works with arrays as well as with individual values.
Let's consider the following example, so that you can better understand what I'm talking about. Suppose, you have a table that lists customer names, purchased products and quantity (Main table). You also have a second table containing the product prices (Lookup table). Your task is to make a formula that finds the total of all orders made by a given customer.
As you remember, you cannot utilize the Excel VLOOKUP function since you have multiple instances of the lookup value (array of data). Instead, you use a combination of SUM and LOOKUP functions like this:
=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Since this is an array formula, remember to press Ctrl + Shift + Enter to complete it.
And now, let's analyses the formula's ingredients so that you understand how each of the functions works and can to tweak it for your own data.
We'll put aside the SUM function for a while, because its purpose is obvious, and focus on the 3 components that are multiplied:
LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)This LOOKUP function looks up the goods listed in column C in the main table, and returns the corresponding price from column B in the lookup table.
$D$2:$D$10This component returns quantity of each product purchased by each customer, which is listed in column D in the main table. Multiplied by the price, which is returned by the LOOKUP function above, it gives you the cost of each purchased product.
$B$2:$B$10=$G$1This formula compares the customers' names in column B with the name in cell G1. If a match is found, it returns "1", otherwise "0". You use it simply to "cut off" customers' names other than the name in cell G1, since all of us know that any number multiplied by zero is zero.
Because our formula is an array formula it iterates the process described above for each value in the lookup array. And finally, the SUM function sums the products of all multiplications. Nothing difficult at all, it is?
Note. For the LOOKUP formula to work correctly you need to sort the lookup column in your Lookup table in ascending order (from A to Z). If sorting is not acceptable on your data, check out an awesome SUM / TRANSPOSE formula suggested by Leo.
Download this LOOKUP and SUM sample
VLOOKUP and SUMIF - look up & sum values with criteria
Excel's SUMIF function is similar to SUM we've just discussed in the way that it also sums values. The difference is that the SUMIF function sums only those values that meet the criteria you specify. For example, the simplest SUMIF formula =SUMIF(A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10.
This is very easy, right? And now let's consider a bit more complex scenario. Suppose you have a table that lists the sales persons' names and ID numbers (Lookup_table). You have another table that contains the same IDs and associated sales figures (Main_table). Your task is to find the total of sales made by a given person by their ID. At that, there are 2 complicating factors:
- The mail table contains multiple entries for the same ID in a random order.
- You cannot add the "Sales person names" column to the main table.
And now, let's make a formula that, firstly, finds all sales made by a given person, and secondly, sums the found values.
Before we start on the formula, let me remind you the syntax of the SUMIF function:
range- this parameter is self-explanatory, simply a range of cells that you want to evaluate by the specified criteria.criteria- the condition that tells the formula what values to sum. It can be supplied in the form of a number, cell reference, expression, or another Excel function.sum_range- this parameter is optional, but very important to us. It defines the range where the corresponding cells' values shall be added. If omitted, Excel sums the values of cells that are specified in the range argument (1st parameter).
Keeping the above info in mind, let's define the 3 parameters for our SUMIF function. As you remember, we want to sum all the sales made by a given person whose name is entered in cell F2 in the main table (please see the image above).
- Range - since we are searching by sales person ID, the range parameter for our SUMIF function is column B in the main table. So, you can enter the range B:B, or if you convert you data to a table, you can use the column's name instead:
Main_table[ID] - Criteria - because we have sales persons' names in another table (lookup table), we have to use the VLOOKUP formula to find the ID corresponding to a given person. The person's name is written in cell F2 in the main table, so we look it up using this formula:
VLOOKUP($F$2,Lookup_table,2,FALSE)Of course, you could enter the name in the lookup criteria of your VLOOKUP function, but using an absolute cell reference is a better approach because this creates a universal formula that works for any name input in a given cell.
- Sum range - this is the easiest part. Since our sales numbers are in column C named "Sales", we simply put
Main_table[Sales].Now, all you need is to assemble the formula's parts and your SUMIF + VLOOKUP formula is ready:
=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
Download this VLOOKUP and SUMIF sample
Formula-free way to do vlookup in Excel
Finally, let me introduce you to the tool that can look up, match and merge your tables without any functions or formulas. The Merge Tables tool included with our Ultimate Suite for Excel was designed and develop as a time-saving and easy-to-use alternative to Excel's VLOOKUP and LOOKUP functions, and it can be very helpful both to beginners and advanced users.
Instead of figuring out formulas, you simply specify your main and lookup tables, define a common column or columns, and tell the wizard what data you want to fetch.
Then you allow the wizard a few seconds to look up, match and deliver you the results. If you think this add-in may prove helpful in your work, you are most welcome to download a trial version by using the below link.
Available downloads
VLOOKUP with SUM and SUMIF - formula examples (.xlsx file)
Ultimate Suite - trial version (.exe file)
423 comments
Hi. I have 16 ranges to test a value and return 16 different values For example, if d2 is between 115 and 120, return 2400. But if d2 is between 110-115, return 2300, and so on.....
I attempted to next if (or(d2>115, d2<=120), 2400, if(....... but I keep getting the error of too many arguments.
SO, if there a better way or can I use the look up if I put my ranges in another sheet?
Thanks so much for the help.
Hii
Can you pls hlp me out.
I req to make a database of commodities sent, their actual rate, quantity consuned, selling price, profit etc.
I am not able to make calculations hoe to use vlookup pls help me out. Its very imp.
Thanx
Can anyone help me solve this. I am a carpenter and have spreadsheet that will compile a list of materials, lengths, and quantities. I want to organize it from largest to smallest, and (type column, then length column) and consolidate like pieces in same row with quantities summed. Ty in advance Norm
TYPE LENGTH QUANT
4 X 12 15 1/2 1
6 x 6 27 1/2 2
4 X 12 39 1/2 3
4 X 12 51 1/2 4
6 X 12 63 1/2 5
4 X 12 75 1/2 6
4 X 12 87 1/2 7
4 X 8 99 1/2 8
4 X 12 15 1/2 9
6 X 4 27 1/2 10
4 X 12 39 1/2 11
4 X 6 51 1/2 12
4 X 12 63 1/2 13
4 X 12 75 1/2 14
4 X 10 87 1/2 15
4 X 12 99 1/2 16
4 X 12 15 1/2 17
4 X 12 27 1/2 18
6 X 14 39 1/2 19
4 X 12 51 1/2 20
4 X 12 63 1/2 21
4 X 12 75 1/2 22
4 X 6 87 1/2 23
4 X 12 99 1/2 24
It was really that easy, and right after I posted this. I figured it out. Thanks!
Hi..I am stuck..this seems so simple but I can't figure it out. I have a main table with a product ID and a cell for freight. Each month I run a report and one particular product ID might have mulitple entries (vertically in the sheet) with a specific freight charge, I need to create a formula to look at the product ID # in my main table and in that one cell next to it total the freight associated with that product ID# from the report I run each month. for example:
A1 A2
Product ID Freight
1 Formula returns freight total
2
3
4
5
6
Lookup Table
Product ID# Freight
1 $2.50
1 $3.50
5 $5.00
5 $5.00
1 $5.00
6 $2.50
So for Product ID# 1 - I need the formula to return $11.00 in the one cell for that product ID# in the main table. For some reason I just can't get it to return even a number.
Thanks for your help
Hi Svetlana,
Thank you for nice tip. I used SUMPRODUCT to avoid using curved brackets.
My question is about array {2;3} , why I cannot use variables instead of numbers? Like {A1;A2}, function returns error in this case.
Thank you
plz can some one tell me i have two columns one contains serial numbers and other sum random values. i want to make a formula that if a user enters a serial number the sum of random number corresponding to it and all the numbers above it should be added and displayed in a cell. the input can be varied. plz can in need of help
Hello, I have a vlookup formula that is working but i want to add a sum to it to not just bring in one value but the sum of those that match. i've been reading lots of stuff online about sumif, sumproduct, vlookup, index, match and i'm so confused. Perhaps mine is difficult because i need the transpose?
First Tab
Cost Center Totals
1
2
3
4
Second Tab
Cost Centers Totals
1
1
2
2
2
2
3
3
hi,
Can anyone give the the guidance for the SUMIF formula, i have put SUMIF formula link from other workbook. after save and closing the work sheet. i tried to open the master file. the link was not updated and showing "#VALUE!"
If i again open the supporting work sheet, then the link was showing the correct value.
How to fix it the issue?
i have pur the following formula
=SUMIF('[Sheet1.xls]SUMMARY'!$D$4:$E$33,A347,'[Sheet1.xls]SUMMARY'!$E$4:$E$32)
Hi Guys,
You People are doing a great job, I like the way you are helping people and sorting out their problems individually, this forced me to share my problem with you people.
I made a workbook consisting of 30 sheets for gas station, i want to keep daily record of that, so i need to know how can i make a final sheet and link those 30 sheets together so they could give me total sales and total expenses of 30 days in the Final sheet ?
Thanks in advance, Looking forward for your help.
I have two files of data. One file contains outstanding loans, only one loan for each customer. The other file contains all the customer loans paid ( a customer could have numerous loans paid). The customer can only have one loan outstanding. I am trying to make sure that the customer didn't have two loans outstanding at the time. To make sure they didn't have an outstanding from one of the paid loans when it was outstanding. Can anyone please Help?
Hi I have a question. i have 2 excel 2010 sheets. the one sheet collects all the data. the second sheet i want the data from the second sheet to be carried over, but this is where i have a problem the data sheet 1, when the data has transfered to the second sheet i want it to close the sheet 1 off so that i can process a new months work on it. the data from the first sheet will have to look for a employee no and his specific salary amount and carry that to a 12 month report and place it in that month. when this is done i want to close of the sheet so that the data in sheet 2 does not change when i clear the sheet 1 (like a roll over clearing the one sheet but keeps the record of it on sheet 2. what do you suggest i use and do i need a vbs and macros for this.
I have an inventory table that I would like to create a summary page for. Consisting of:
Column A Column B Column C
Qty Rec'd Part Number Total Rec'd
There are several rows of the same part number containing different qty's rec'd in each. I would like to be able to have a formula that would search the whole (same) worksheet for that part number and total the qty's in Column C.
Thank you in advance for your help ☺
hello!
i want to use add the values for the particular item appearing several times in a table. how is ti possible. see table below. for example A was produced two times. i want to use count function to know how many times A was porduced and then add the quantity for A is one location.
element Prdouced
A 5
B 3
X 5
X 15
B 11
A 20
HI SVETLANA
I have budgeted data in one sheet having Budgeted amount of all account cods and actual data having a lot number of same codes in other sheet, I want to link other sheet with budgeted data and Want to sum up all the amounts of same codes in second sheet actual data and show parallel in next column of budget sheet. Can you please advise how can I do?
For Example
Sheet One Budget Data
Account # Budget Amount Actual Amount
1 50000 sum of account#1 from second sheet (actual data )
2 15000 sum of account#2 from second sheet (actual data )
3 30000 sum of account#3 from second sheet (actual data )
Second sheet actual data
Account # Budget Amount
1 5000
2 2000
1 6000
1 3000
3 4000
2 5000
1 2000
3 3000
2 7000
regards
Khan Afzal
Hi Svetlana Cheusheva and thanks for the extremely helpful tutorial. Referring to the first table above, I'd like to know whether we can have a formula that tells us the average sales for a particular product within a particular time period. For e.g.,what was the average sales of Oranges during the period March to June?
Similarly, on changing the words "Oranges","March", and "June" in 3 adjacent cells, to, say, "Lemons","April","August", I get the result of "Average sales of Lemons for the Period April to August".
Sheet 1
Collected by Designation Ref CR/DR Credit
S&L Head Of Unit 8089 C 250,000
Hannington Manager 8090 C 400,000
refund Team Leader 8091 C 471,805
PSU Officer 8092 C 488,583
Alex Head Of Unit 8093 C 500,000
Hannington Manager 8094 C -
refund Team Leader 8095 D 157,261
CL Officer 8096 D -
Joseph Head Of Unit 8097 C 300,000
Summary sheet
Name Grade Total
Hannington Head Of Unit ......
Alex Manager ......
Joseph Team Leader ......
Jimmy Officer ......
I would like to get cummulative collection per collector in the summary sheet.
Thanks for this insight. I request to be assisted I have two excell sheets both having a name and designation, I would like to summarise the amounts collected per name because we run a credit collections firm and we would like to award incetives.
Joshua
plz help me how to In this sheet A1 to F1 value are below 18 its is BOLD and G1 = total of A1:F1 and H1=percentage of G1
i1= below 18 value of total numbers
I want if i1=0 then show the value of H1 or average of G1 and If i1 is not euqal to zero(0) show value of i1 or below number of values
I am trying to use a combination of SUMIFS and VLOOKUP but I can't seems to get it to work. Usually, this or other forums can always give you an example of someone who has had the exact same issue, but in this case I can't find a solution among previous examples. So, need some expert help on this :)
I have two seperate data files, these are extractions from our order program, and they are two different 'reports' from this order system.
The problem is that each file contains different type of information about the orders, and I need to merge them by the help of formulas.
A simplified example (the original file contians alot of data, and this calculation needs to be done once a week, so therefore I would rellay like to able to 'automize' it):
In the first file, columns of interest are: Order number, Order value, Seller-ID.
In the second file, columns of interest are: Order number, Order method
I want to in a seperate sheet (or workbook) be able to summarize the total order value for each Seller-ID, depending on if the order has been created by phone or by mail.
I have been trying with SUMIF and including a VLOOKUP inside, since I need to make a cross-reference between the first and the second file, using order number as the common factor.
Any thoughts or ideas?
My query is after using this functions is there a way to view which all values are getting added to a cell after using the "SUMIF" function. Eg: my cell displays 800 which is derived using SUMIF function & 800 displayed by adding 3 enries 400,300,100
how can i view these values?is there a way to it.
kindly guide for the above query.
Hello! I have two spreadsheets now
Sheet1 contains countries and other datas
sheet2 contains a full list countries and their respective regions
e.g.
Afghanistan Asia
Albania Europe and Central Asia
Angola Africa
Antigua and Barbuda Americas
Argentina Americas
*my question is how can I define countries by region in SHEET1 by using the list in sheet2
sheet 1
Invoice number-------Invoice Date------- Invoice Amount------ Total Payments
123121---------------02/10/2014----------50,000---------------?????
524514---------------05/11/2014----------80,000---------------?????
659874---------------08/11/2014----------60,000---------------?????
sheet 2
Invoice Number-------Payment Amount
123121---------------5,000
659874---------------6,000
659874---------------7,000
123121---------------3,000
524514---------------2,000
524514---------------6,000
dear
can you help me applying this formula plz
i have two sheets
1. invoice sheet
2. payment sheet
Invoice sheet has below
Invoice number Invoice Date Invoice Amount Total Payments
123121 02/10/2014 50,000 ?????
524514 05/11/2014 80,000 ?????
659874 08/11/2014 60,000 ?????
Now the Since the payments are in installments which are present in another sheet
Invoice Number Payment Amount
123121 5,000
659874 6,000
659874 7,000
123121 3,000
524514 2,000
524514 6,000
Now i Want to apply vlookup in sheet 1 taking totals of payments against each invoice
Kindly help me with this thank you
Hello,
How do I figure out my average sale for a November but for selective days? example: if i want Mondays for all of November?
Date Sale
Monday Nov 01, 2014 $150
Tuesday Nov 01 , 2014 $450
ect...............
Hello Peter,
The SUMIFS / SUMIF functions won't do in this case. Try the following array formula (remember to press Ctrl + Shift + Enter to complete it):
=SUM((--MONTH($A$2:$A$100)=11)*(--WEEKDAY($A$2:$A$100)=2)*($B$2:$B$100))
Where $A$2:$A$100 is the column with dates, $B$2:$B$100 is the sum column. BTW, November, 1 is Wednesday : )
Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 Total
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
1 2 3 4 5 6 7 ????
Hello,
I need to make a function which will calculate the data till month and will display in Month column. how could i make the function on this?
for eg, let say, Current month is November, now i want whenever i open the excel document, Excel should auto calculate and sum the data oct and nov month data and display in total column)
Could anyone help me on this?
Hello Nitij,
Please specify if you need to sum data for the last 2 months, for the current month or anything else? If the row contains only numbers, you can use the SUM function, e.g. =SUM(A2:G2).
Please give the four arguments when using the VLookUp Function.
Svetlana,
That is exactly what I was looking for! Thank you for your help.
Thank you,
Anya
Hi,
I read your post and didn't understand it fully. Although that's because i am searching for something else to work with. My problem is this particular excel sheet.
Delivery date Delivery amount Current Date Maturity
29-10-14 2,675.00 21-11-14 23
29-10-14 4,320.00 21-11-14 23
29-10-14 1,235.00 21-11-14 23
29-10-14 1,235.00 21-11-14 23
07-11-14 1,636.00 21-11-14 14
06-11-14 26,499.60 21-11-14 15
07-11-14 1,440.00 21-11-14 14
11-11-14 48,293.00 21-11-14 10
06-11-14 24,888.00 21-11-14 15
11-11-14 60,092.08 21-11-14 10
11-11-14 46,552.80 21-11-14 10
11-11-14 16,054.80 21-11-14 10
11-11-14 25,937.58 21-11-14 10
11-11-14 24,888.00 21-11-14 10
11-11-14 26,484.46 21-11-14 10
11-11-14 11,638.20 21-11-14 10
11-11-14 11,382.28 21-11-14 10
12-11-14 22,680.48 21-11-14 9
12-11-14 40,007.80 21-11-14 9
12-11-14 11,963.28 21-11-14 9
12-11-14 11,460.00 21-11-14 9
12-11-14 4,416.60 21-11-14 9
13-11-14 13,301.88 21-11-14 8
16-11-14 24,888.00 21-11-14 5
19-11-14 22,083.00 21-11-14 2
19-11-14 6,329.88 21-11-14 2
15-11-14 22,341.40 21-11-14 6
Here, i need the current date to calculate maturity when maturity term is 21 days and then i need to sum up total delivery without those amounts that have been paid and have not matured yet. I tried using VBA editor to exclude colored cells but so far i can only add colored cells but not meeting the criteria of 21 days of maturity. Please help if you can.
Advance thanks
Shahriar
Hello Abul,
I believe your date did not post correctly here. So, if you send a sample workbook to support@ablebits.com and include the result you want to get, our support team will try to help.
Hi, I have this table I want to add the total numbers in column titled AL if the equivalent date is current year only and dont sum up the old years. Example
Total Leave Used: ____ (here I need the formula cell I9)
FROM UNTIL EMP REASON AL MC HL UL LATE
04/07/2012 04/07/2012 21 ANAK SAKIT 0 0 0 1 0
15/07/2012 15/07/2012 21 PERSONAL MATTER 0 0 0 0.5 0
25/07/2013 26/07/2013 21 RAYA HOLIDAY 0.7 0 0 0 0
30/07/2014 31/07/2014 21 RAYA HOLIDAY 2 0 0 0 0
01/08/2014 02/08/2014 21 RAYA HOLIDAY 1.5 0 0 0 0
If the year in 1st column and second column is equal to current year (this year 2014) then calculate all the AL column ELSE if got 2013 or 2012 dont count the AL. Count only current year so if 2015 the 2015 only will count and answer will be on cell I9.
Thank you.
Hello Svetlana,
I have the following tables:
Table 1.
A B C
Date Name Value
7/21 Luke 20
7/21 Kip 18
7/23 Luke 19
7/23 Kip 10
7/25 Eric 8
7/26 Eric 13
Table 2 (unique names from table 1)
A
Name
Luke
Kip
Eric
I need to calculate sum of values from table 1 for each name in table 2, but only sum of values that are higher than 10.
Thank you so much in advance for your help!
Anya
Hello Anya,
You can do this using the SUMIFS formula that allows calculating the sum based on multiple conditions. Here is the formula for cell A2 in sheet 2:
=SUMIFS(Sheet1!$C$2:$C$7, Sheet1!$B$2:$B$7,A2, Sheet1!$C$2:$C$7, ">10")
It will return the sum of values for Luke higher than 10, in your example it's 39.
Modify the ranges in the formula according to your real data and copy it across column B in sheet 2. Hopefully this is what you are looking for.
BRAND BATCH Q P N PROD.
MPLW(CLS) 30 0 0 171
MPLW(CLS) 31 0 0 1102
MSW 89 947 1280 1504 3731
SPW 12 0 62 337 399
ABW 11 0 0 304 304
MSW 89 0 550 1075 1625
MSW 90 0 930 1818 2748
SPW 12 0 0 404 404
MSW 90 1071 0 2223 3294
MPLW 32 0 1563 677 2240
RCDW 10 365 122 0 487
MSW 90 500 600 1188 2288
MSW 91 591 857 1866 3314
RCDW 10 0 0 346 346
MSW 91 0 0 2466 2466
MPLW(CLS) 32 693 0 0
SPW 12 300 350 0 650
MSW 91 0 0 1500 1500
ABW 11 49 100 155 304
SRW 18 0 0 120 120
SPW 12 0 0 238 238
MSW 91 0 0 1097 1097
MSW 92 1070 0 2183 3253
ABW 11 325 0 0 325
ABW 12 0 225 0 225
MSW 92 0 1310 2911 4221
ABW 12 0 133 224 357
RCW D 10 0 214 0 214
how i calculate batch wise total vale from above sheet
BATCH Q P N
RCDW 10 value
SPW 12
SRW 18
MPLW(CLS)
31
32
MSW 89
90
91
92
Hello,
I have a question regarding summing together values on a given date from multiple sheets to a summary sheet. I have tried a sumproduct but keep getting a #REF. The date is in the same column in each sheet (Column M) and the amount to be summed will be in the corresponding column P so I need to Vlook up that date. The date is in different cells each time. I have numerous dates and 50 sheets to sum so do not want to have to sum them manually.
Any help that you could provide would be much appreciated.
Thanks
Andrew
Hello Sayyid,
This makes the task much easier since you don't need the VLOOKUP function.
The following SUMIF formula does what you need:
=SUMIF(B2:B12,"s-003",C2:C12)
Instead of putting a particular ID in the formula, you can add a cell reference containing it, like this:
=SUMIF(B2:B12, B2, C2:C12)
Where B2 is the cell with the ID you want to sum.
For more info about Excel SUMIF, please check out this article:
How to use SUMIF in Excel - formula examples
Thank you very much for your answer
Hello,
I have data in sheet as bill,name,item,qty,price
In sheet to I have bill , name I want to sum the price by bill number how to use it
Hello,
I have data in sheet1 as bill,name,item,qty,p.price(different price will be in same invoice it's because profit diff )
In sheet 2 to I have bill , name I want to sum the price by bill number how to use it
Hello,
I have question regarding the last method,VLOOKUP and SUMIF.
How would you write the formula if there is only one table (main table) and you want to find the sum of all the corresponding values of a particular ID?
Help me out?
Thank you.
Hello Svetlana,
I am trying make use of this VLOOKUP AND SUM method in one of my excel sheet to create a summary of the items.
But I am not able to make the right formula for it.
Could you please help me with this?
Example:-
CODE IN
101101 420
101101 362
101101 83
101101 129
101102 42
101102 53
Lets say 101101 and 101102 come under a single category.
How do I look up these specific codes in an excel sheet which has many other codes and add their corresponding values?
Looking forward to your kindness.
Hello Sahal,
You can fulfill your task by creating a simple pivot table. Go to INSERT > PivotTable. When the PivotTable pane appears, drag the Code column into the Rows section and the IN column in the Value section.
Svetlana,
Could you please further explain your Tip above referencing [@Product] as a table? I don't understand exactly what steps are being taken, what you are attempting to accomplish or how it saves time. I feel like I might need to know this timesaver.
Hello!
I simply converted my range of cells (B1:C9 in that example) into a table. To do this, just select the cells, go to the Insert tab and click Table.
"Product" is the header of column A, as you can see in the screenshot. When typing a formula in the formula bar, Microsoft Excel inserts the table column header automatically (with the preceding @) once you type a cell reference that belongs to your table.
As for saving time, when you write or copy a formula in just one cell of the table, the entire column fills down with the same formula automatically.
Hi Svetlana,
Your site is excellent and a great help...my question is 16 columns of data as follows:
Col 1 - Lookup unique item
Col 2 - Data Value
Col 3 - +/- adjustment to col 2 value
Col 4 - Adjusted value
Cols 5, 6, 7 -- Same as Col 2-4
Cols 8, 9, 10 -- same as above and pattern repeated 2x more (so a total of 5 times)
What I need to do is find the min and max adjusted values, and exclude them then calculate the average of the remaining 3 adjusted values.
I have created array formulas to identify min and max values
{=MIN(VLOOKUP($A4,$A$4:$Q$21,{4,7,10,13,16},FALSE))} same thing for max values. But dont know how to construct an AverageIF statement to first exclude max and min values then calculate the average of the remaining 3 values in each row for each unique lookup item. Can you help or do you have any suggestions.
Thanks much
Hi Svetlana Cheusheva,
I am stucked in problem , where I have cheq number and Amount in one sheet 1
and in another sheet 2 also I have cheq number and Amount.I want to find the records that have cheq number and Amount ( both) matching in sheet 1 , when we compare two sheets. I tried using diff formulas but did not get the desired result. Can you please help.
Thanks in Advance.
Atul
Hi Atul,
Please check out the following examples of how to search by multiple criteria in Excel:
https://www.ablebits.com/office-addins-blog/vlookup-formula-examples/#vlookup-multiple-criteria
https://www.ablebits.com/office-addins-blog/excel-index-match-function-vlookup/#lookup-multiple-criteria
Hi,
I am working with a huge spread that consists of only figures. There are various expense lines coded with debit and credit amounts. Can you kindly suggest to me which formula to use in summing the debits and credits of each expense line in another spread sheet using their respective expense codes.
i Have Two Sheets from which i need to use Vlookup Formula to get the Value, But i dont know to add Multiple items,
Sheet 1
Staff# Sales Commission
1001 100000 1000
1002 20000 200
1003 30000 300
1004 10000 100
1001 20000 200
1005 10000 100
1006 30000 300
1007 20000 200
1001 20000 200
Sheet 2
Staff # Formula Commission
1001 =VLOOKUP(A2,Sheet1!$G$3:$I$11,3,0) 1000
1002 =VLOOKUP(A3,Sheet1!$G$3:$I$11,3,0) 200
1003 =VLOOKUP(A4,Sheet1!$G$3:$I$11,3,0) 300
1004 =VLOOKUP(A5,Sheet1!$G$3:$I$11,3,0) 100
how add multiple amounts in this issue.
I am trying to do the following which I think is fairly basic:
I have two reports. One is a budget file the other tracks sales.
In the report that tracks sales I want to sum the YTD budget from the budget file on any given month ,when the cell in question returns the month.
Any advice gratefully received.
H
Thanks for sharing tutorial, helpulness..
Hi,
was wondering if you could help me with a an issue I have using SUMIF function. Range B10:B264 I have a list of item names, it can be up to 5 diffent item names. I have a single cell with one of those item names in it on Cell B278. I have some columns for calculations, stage #, straps, deliveries and usage amount (H10:H264) per item name. every row from 10 to 264 has all that information for for every stage # for that group of items, not following any particular order. can be up to 50 stages per item. what I need is a sum of the usage amount for one particular item name throught all the stages, item names and stage #'s come in no particular order. this is what I came up with:
=SUMIF(B10:B264,"=B278",H10:H264)
but it gives me a " 0 " amount as the result. now the usage amount range is based on a formula itself, but provides the number I need to sum throughout.
I also tried to use this other function:
=SUMIF(B10:B264,VLOOKUP(B278,B10:H264,7,FALSE),H10:H264)
but got the same result as the previous mentioned formula.
You think you can help me out?
Thanks
Hi Svetlana,
Thank you for the awesome tutorial. It is very helpful.
I am working on something similar in your Lookup & Sum example for my research project.
I need to find the sum of quantity of a particular category per customer. In lookup table I have customer id in one column, quantity in another and category in third column. And in the main table, customer Id and all the categories horizontal way(rows). So my task should be find a the total quantity for a category for a customer. Can you please help me with this?
Thank you.
Hi sir,
There is a main table item list, i need to SUM up QTY by each item of month from raw table. I have difficulty to bring out a raw table data which show all items of a year with Item Number, QTY and Date. Pls advise.
Hello Michael,
If you can send me your sample workbook and the result you are trying to achieve at support@ablebits.com, I'll try to help.
hi,
this is shoeb and i want to make formula for below table for sum from date to date
column a column b column c column d column e
Location 16-Sep-14 17-Sep-14 16/09/2014 17/09/2014
Axis Airoli 3rd 0 957 0
Axis Airoli 4th 1113 1758 1080 result should be
Axis Airoli 5th 1205 1237 686 =sum of above dates value
Axis Worli 1st 1499 1790 1522 with match of column a
Axis Worli 2nd 1433 1844 1402
please help to build up this formula
Regards
shoeb
Hello Shoeb,
I am sorry, your data in the comment got distorted. For us to be able to assist you better, please send your workbook to support@ablebits.com and include the result you expect to get. We'll look into the issue.
Hi
I have two worksheets.
One listing all days against a individual contract e.g.
Contracts Days
1234 1
1234 4
1234 2
1234 9
1235 1
1235 6
1235 0
1235 11
1236 7
1236 2
1236 20
1237 1
1237 7
1237 5
1237 5
and the other where I want to look this days total up for that contract e.g.
Contract Total days
1234 16
1235 18
1236 29
1237 18
What VLOOKUP & SUM formula would I use as column one would not work as it is SUMing rows.
Thanks
Sarah
Hello Svetlana, I have the below database with more than 500K rows of information. I need to build a report with months in a row not columns, and from the DB get all sales per country by month and by code
ColumnA ColumnB ColumnC ColumnD
Month Country Code Qty_Shipped
Apr-11 France 2525 15
May-11 Switzerland 3121
Jun-11 Poland 1852 987
Jul-11 Italy 3315 159
Aug-11 Russia 4597 654
Sep-11 Holland 1548 289
Oct-11 England 6594 264
Nov-11 Norwegian 43626
Dec-11 Spain 1825 123
This is the model I need. Can you help me to build a formula?
Country Code Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11
The database I put only one month per year. The reality is that this is a database with 500K rows the smallest one.
Can you help me with ideas?
Thanks
Hello,
If my understanding is correct, you need to get "Country Code Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11" from "Month Country Code Qty_Shipped". If so, you can create a pivot table based on your first table:
1 Select your table.
2 Insert a pivot table (go to INSERT > Tables > PivotTable).
3 In the PivotTable Fields pane, drag the Code column to the ROWS section, the Month column to the COLUMNS section, and Qty_Shipped column to the VALUES section.
If you get "Count of Qty_shipped" instead of "Sum of Qty_shipped", click on the field name, select "Value Field Setting" from the menu, choose Sum from the list "Summarize value field by" in the pop-up dialog and click OK.
The result will be similar to this:
pls send the formula vlookup total amount in word
Hi Amit,
Sorry, I do not understand what exactly formula you want. Anyway, you can download a corresponding Excel workbook (a download link is provided at the end of each example) and copy the formula from there.
Hi,
Pls. send how to calculate sume based on vertical & Horizontal criteria
Hello Svetlana Cheusheva,
I am calculating something like you mentioned in the part "LOOKUP AND SUM - look up in array and sum matching values". And, I applied the formula you provided:
{=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))}
However, the outcome seems incorrect. I finally found that the LOOKUP function must be used with sorted lookup table (in ascending). In addition, the total amount that Lilly Smith (in your example) spended should be $122.59 instead of $106.45. The reason of discrepancy is caused by LOOKUP function returns wrong value when the lookup table had not been sorted.
So, do you think is there still any ways that I can get the correct result using one formula only and without sorting the lookup table?
Also, you mentioned that VLOOKUP function cannot be used in array formula but LOOKUP function can. I wonder is there some tricks that I can know immediately which function can be used in array formula or not??
I really appreciate if you can reply me.
Thank you.
Hello Leo,
You are absolutely right about sorting the lookup table in ascending order. I completely missed this point when writing the formula, a shame to me and my apologies. I've added a corresponding note and updated the example. Thank you very much for pointing out this error!
As for which functions can be used in array formula, I do not know such a trick. I can only say that MATCH does not work with arrays either, which is why the INDEX and MATCH combination that often helps when VLOOKUP fails does not work in this case. And I cannot figure out any way other than sorting the lookup table and using the LOOKUP and SUM functions.
Thank you for your reply and teaching!!!
Also, my friend recently taught me a way to get this type of total amount. The following is the formula:
{=SUM($D$2:$D$10*IF($C$2:$C$10=TRANSPOSE('Lookup table'!$A$2:$A$16),TRANSPOSE('Lookup table'!$B$2:$B$16),0)*($B$2:$B$10=$I$1))}
The core is using TRANSPOSE function to make the vertical lookup table to be horizontal. As a result, we can multiple the vertical array (the Main table) and the horizontal array (the lookup table) to create a 2-dimensional array formula.
Wow! I would never think of using TRANSPOSE. Thank you very much for sharing!
hi, I want to create an invoice on excel sheet. on another excel workbook I want to create my stocklist. how do I link the 2 together, sothat when I fill in the invoice, the stock on the other workbook automatically changes. please help.
thanks for correction on sorting error, but did'n explain why need to sorting required? please explain.