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 4. Total comments: 441
i have a problem were I need to lookup sales made by a store for certain date and for a certain date there are various sales made by the various store and I need to add all sales made by a certain for a certain date and there a various stores eg
date store
Need to Calculate Above 45 Days Value Total Sum from Next Sheet Row it is possible in formula?
Hi
I want to make data like as below mentioned can anybody please help ??
Sheet -1
S.MAN NET AMT.
Modern Trade 16344.00
Modern Trade 8847.00
Modern Trade -23402.00
Modern Trade 715.00
DSE002 Dilli 3574.00
DSE002 Dilli 18352.00
DSE002 Dilli 16046.00
Manoranjan S -16344.00
Manoranjan S 16361.00
Manoranjan S 3079.00
Sub-D 726.00
Sub-D 1054.00
Sub-D 7841.00
Sheet -2
S.MAN Total sale Total -ve sale
Modern Trade
DSE002 Dilli
Manoranjan S
Sub-D
i want to add the values under a same ID in a particular column. Kindly give me the right formula.
Hello,
Please somebody tell me how to create formula in excel for my data. Firstly It needs to find the matching word in that column and need to sum adjacent cell data of matching word.
Thanks
1ST SHEET
MATERIAL CODE USAGE
INV001 1
2ND SHEET
DATE ISSUANCE MATERIAL NO. QTY
04-May-19 INV001 1
14-May-19 INV001 2
18-May-19 INV001 1
I USE =SUM(VLOOKUP([@[MATERIAL CODE]],USAGE!C:E,3,1)) TO TOTAL ALL INV001 IN 1ST SHEET BUT ONLY 1 IS THE RESULT.WHAT FORMULA CAN I USE TO TOTAL ALL INV001 IN 1ST SHEET AFTER I UPDATE THE 2ND SHEET.PLEASE HELP THANK YOU.
sheet 1 has full details of all issuing item for the month want to look up sheet 2 as each item how many times issued particular item as one total
Sheet 1
Col A = Item Number( Same item Repeat many days(1001,1001,1002,1001,1003,1001)
Col B= Qty issued (Repeat Many Days)
Need Sheet 2
Col A = Item Number ( 1000,1001,1002,1004, etc.)
Col B = Subtotal of Qty issued for particular Item
his it possible in Vlook Up , If So please update formula
Thank you SO much for this! Helped me immensely in a complicated sheet today :)
Dear Sir,
GST TAX% TAXABLE VAL.
12% 11733.12
5% 2095.20
12% 2304.72
5% 5238.00
5% = Total Value (In Single Cell)
12% = Total Value (In Single Cell)
How to sum if same types Tax % Value is calculating in multiple cell in excel single cell Tax % Wise
Please help us.
I have 2 sheets. the first (key and total$) has a list of product numbers in the first column. in column "I" I want to place the calculation that gets the product number from column "A", goes to sheet2, matches the product number in column "B" (there are duplicate product numbers), then grabs the corresponding $'s from column "R" (sheet2). and if there is a duplicate product number on sheet2 it will sum them together. I went through your sumif/vlookup instructions and cant get it to work. all help will be appreciated.
Hi.
I have an area that I use to lookup entries from a table with data validation. The values selected I want to use to lookup and sum values from another table. I want to:
Lookup these "Item" using data validation
Item 7
Item 8
Item 7
Then look in this table for each Col value selected
XL L M S XS
Item 7 1 1 2 3 0
Item 8 2 3 3 0 0
-Item 14 0 2 1 3 8
Item 7 0 2 0 3 12
and sum various sizes here from table above
XL L M S XS
4 4 5 3 0
Can I use lookup and sumifs in an array formula?
Thanks,
Olafur
Hello!
I am trying to use VLOOKUP and SUM/SUMIF to sum all the values in one column based on criteria in the first column. Example:
Column 1 Column 2
Avocado 10
Roasted Chicken 10
Mozzarella 5
Roasted Chicken 5
I need to sum column 2's number of Every instance of Roasted Chicken.
using (SUM(VLOOKUP(A4,'PI - MLK '!$A$2:$2$5,2,FALSE))) I only receive the value of the first roasted chicken. How can I capture every instance?
Thanks,
Chase
check out the sumif function
Hello,
I need assistance with the following please:
In one cell, I have an addition of: ((0)+(0)+(10))+((13)+(0)+(0))+((0)+(0)+(10)).
I would like to only add the addition of the third parenthesis from every outter parenthesis so the total would give me 20.
Do you guys have any suggestion for this approach.
Thank you.
please
I need to get the values of (A1 Staff costs) in by month in below table. please help me
12/20/18 A1 Staff costs 4,412.00
12/20/18 A1 Staff costs 30,232.00
12/20/18 A1 Staff costs 4,412.00
12/20/18 A1 Staff costs 141,000.00
01/10/19 B6 Travel 124.25
01/10/19 B6 Travel 3,890.19
01/10/19 B6 Travel 570.00
01/10/19 B6 Travel 401.00
01/10/19 B6 Travel 3,381.50
01/10/19 A1 Staff costs 6,300.00
01/10/19 A1 Staff costs 7,613.66
I need to get the values of (A1 Staff costs) in by month in below table. please help me
12/20/18 A1 Staff costs 4,412.00
12/20/18 A1 Staff costs 30,232.00
12/20/18 A1 Staff costs 4,412.00
12/20/18 A1 Staff costs 141,000.00
01/10/19 B6 Travel 124.25
01/10/19 B6 Travel 3,890.19
01/10/19 B6 Travel 570.00
01/10/19 B6 Travel 401.00
01/10/19 B6 Travel 3,381.50
01/10/19 A1 Staff costs 6,300.00
01/10/19 A1 Staff costs 7,613.66
Hi,
I have the chart below
Employee name:
Sick Taken :
Vacation Taken
Staff Name Type # of days From To Notes
Josephine Domingo Vacation 1.0 1-Jan-18 1-Jan-18 Approved
May Flower Vacation 0.5 1-Jan-18 1-Jan-18 Approved
Josephine Domingo Sick 1.0 1-Jan-18 1-Jan-18 Approved
May Flower Vacation 3.0 1-Jan-18 1-Jan-18 Approved
Josephine Domingo Vacation 4.0 1-Jan-18 1-Jan-18 Approved
What formula can I use to calculate the sum of Vacation days or Sicks days for a specific employee?
Any help will be helpful :)
Sir,
I have applied this in rec.sheet and value voolup sum from 3 sheets.
=SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-1'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-2'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-2'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-3'!$J$4:$J$142)
This works fine but need better way.
Plz give me better suggestion
I need tinmtime sheet calculate with vlookup&sum please help me
Column Column Column
A B C
Karim 100 200
Rohim 200 300
Karim 100 500
Rohim 500 100
Result should be:
Karim 900 (100+200+100+500)
Rohim 1100
I used = sumproduct(Vlookup (A1,A1:C4,{2,3},0))
But it does not work. How can I do it? Pls help
Yes! Paul, Same Problem With Me :), And I Need Distinct Name in Drop-down List
A B C
1 2019.12.02 Ronaldo 50
2 2019.12.02 Messi 20
3 2019.12.03 Ronaldo 50
4 2019.12.03 Messi 22
5 2019.12.04 Pele 15
6 2019.12.04 Ronaldo 44
use data validation function
I have many columns like; date, ID, fullname, customer_type, any_details
Now I want to make a form to add/ edit/ search/ delete data, Some time I would have to sum up customer type in a specific dates or month or year.
Want to do vlookup with checking lookup values and if dupilcates values find then it balance the duplicates values,else it write as single values.
E.g. A B Qty output
a1 d123 1500 1000
a2 d123 700 1000
a3 d123 400 600
a4 d124 200 200
a5 d125 300 300
A) Complete the cost, subtotal and total of the follwing table using the corresponding formulas.
"B) Introduce today's date using the corresponding formula next to the cell ""Cost"" "
C) Change the currency from euros to Pound Sterling
Quantity Description Unit Price Cost
3 Valve 1,500 €
5 Column 500,000 €
6 Clamp 980 €
2 Turbine 200,000 €
1 Compressor 100,000 €
2 Tank 300,500 €
3 Tube 6,750 €
Subtotal
VAT 21%
TOTAL
I am trying to perform a vlookup of order numbers where I have a couple of duplicates which I want to sum into one number in my final table. I have tried playing around with
=IF(COUNTIF($P4:$V2598,$E4)>1, "Multiple results" ,VLOOKUP($E4,$P$4:$V$2598,6,FALSE))
But all I can do is print a message rather than displaying the result of my addition. Any ideas?
Hi everyone how can i consolidate the multiple duplicates with different values I need to sort out with the following ex below: I need the output that sums the total male > total female and total unkown under sample 1
list gender value
sample1 male 2
sample1 female 3
sample1 unknown 4
sample1 male 2
sample1 female 3
sample1 unknown 4
sample2 male 5
sample2 female 6
sample2 unknown 7
Glenn:
Where your sample data is organized like this:
Sample is in A30:A42, Gender is in B30:B42 and Value is in C30:C42 enter this formula in an empty cell:
=SUMIFS(C31:C42,A31:A42,"=Sample1",B31:B42,"=Male")
Hello,
I am currently trying to Sum number on a sheet (SHEET 1) within my workbook. On the same row of the amount that I want to sum is a specific description to that amount.
I am trying to SUMIF this amount to a different sheet (SHEET 2) corresponding to the matching description on the row from the other sheet (SHEET 1).
The amount varies by each entry. The matching descriptions will also vary.
I am trying to do this by keeping my filters and avoiding making tables for my data
I have Dates as column headers in a sheet. In another sheet I have to input date and I want a formula to search the relevant column on the basis of date input and sum the entries using a criteria on another colum simultaneously.
Hi,
Please, I will so much appreciate if you could help explain the use of the "Info!" excel function works in the below formula:
=VLOOKUP(P6,Info!C:I,7,0)
Many thanks.
It's not an Excel function, it's the sheet where the data is located.
Hi Sir/Mdm, I'm using =SUM(VLOOKUP(B2,A6:U105,MATCH(D2,A4:U4,0),0))"
to find a certain cell value. However, I need to sum up the values of the cells from B2 value till B3 Value. How should I do it?
Eg.
B2 = 1950
B3 = 1975
D2 = Metal
I need to enter value into Cell B2, B3 and D2. With the details entered, I need to sum up the value from "value of Row B2" to "value of Row B3" for the column of "value of Column D2".
Dear Sir,
Please give me suggestion/help for stock inventory sheet
Name of contractor Apple Banana Etc
Ram 2 5 4
Shyam 4 3 5
Hari 3 2 1
Ram 5 6 6
Hari 2 3 5
Saral 4 4 3
Ram 2 5 4
Shyam 4 3 5
Hari 3 2 1
Ram 5 6 6
Hari 2 3 5
Saral 4 4 3
I want to Total Issue to
"Ram"
Apple
Banana
Etc
Which formula's tell me sir.
Hi
I am trying to add total quantity sold by Name and ID same person.
=SUMIF('Q-1'!$B$4:$B$137,B4,'Q-1'!$K$4:$K$137)+SUMIF('Q-2'!$B$4:$B$137,B4,'Q-2'!$K$4:$K$137)+SUMIF('Q-3'!$B$4:$B$137,B4,'Q-3'!$K$4:$K$137)+SUMIF('Q-4'!$B$4:$B$137,B4,'Q-4'!$K$4:$K$137)
How can i short this formula with same answer?
i want to do total of 4 different sheets vlookup value.
Thanks
anybody help me please?
i have data below…
table1:
==========
ITEM | TYPE
==========
a1 | Cash
a2 | Cash
b1 | AP
c1 | AR
table2
=============
ITEM | AMOUNT
=============
a1 | 100
b1 |-100
a2 | 50
a1 | 40
b1 |-90
c1 | 200
result:
=============
TYPE | AMOUNT
=============
Cash | 190 (sumif?)
AP |-190 (sumif?)
AR | 200 (sumif?)
how do I populate a 'sumif' formula in the result table?
thanks & regards
i have used simple sumif formula and it works
as i have a table of values in one column (range 2) and criteria (range1) two and two cells out side as one for selecting the listed criteria and one for result on same sheet and applied the formula =sumif(range1, criteria, range2) in the cell where i expect the result.
thanks
Thank you very much for sharing.
how to use iferror and vlookup in excel for counting
Item Code Item Description
A1 Processors
A2 Printers
A3 Motherboards
This sample is belong to sheet1
Item Code Item Description
A1 vlookup formula pls!
This sample is belong to sheet2
what is the exact formula for the "item description" column in sheet number2, if I enter A1 on the "item code" column? the Processor should be the output.
Hello,
If I understand your task correctly, please try the following formula:
=VLOOKUP(A2,sheet1!$A$2:$B$4,2)
Hope this will help.
Item Code Item Description Item Code Item Description
A1 Processors A1 vlookup formula pls.
A2 Printers
A3 Motherboards
This sample is belong to sheet1 This sample is belong to sheet2
what is the exact formula for the "item description" column in sheet number2, if I enter A1 on the "item code" column the Processor should be the output.
I am looking to look up a product and calculate how much of it will be used.
I have Thousands of rows and 25+ collums
For example
MAIN BREAKDOWN
One & Half brick Walls
Bricks 10000
Cement 100
Building Sand 1000
One Brick Walls
Bricks 12000
Cement 120
Building Sand 1200
Half Brick Walls
Bricks 2500
Cement 15
Building Sand 15
Half Brick Walls
Bricks 2500
Cement 15
Building Sand 15
Half Brick Walls
Bricks 2500
Cement 15
Building Sand 15
BILL OF QUANTITIES (Seperate Sheet)
Bricks Sum of all Bricks
Cement Sum of all Cement
Building Sand Sum of all Building Sand
I need to add all of those up with out individually summing each one
There are over 200 rows that mention bricks
and we continually add in at various points of the sheet.
PLEASE HELP!
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
in MS excel If there are three columns A, B and C, I need to get their sums to a new sheet which ones heading comes as Column B details Look up one as column A, need to create formula to get sum for column C.
in MS excel If there are three columns A, B and C, I need to get their sums to a new sheet which ones heading comes as Column B details Look up one as column A, need to create formula to get sum for column C.
A 10 25
A 8 30
B 5 45
A 10 30
10 8 5
A 55(Formula for this Number)
Hello,
I need to do almost exactly what you describe Under LOOKUP AND SUM - look up in array and sum matching values. However, while my lookup table is in fact vertical, my main table is horizontal. Hence, the formula you provide: {=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))}, does not work.
Any ideas?
Thanks!
I want to lookup the 593027761 value in Sheet 1 and sum of all the values in
Sheet 2 for lookup value in Sheet 1
Workbook 1
593027761
Workbook 2
593027761 100
593027761 200
593027761 600
593027761 5485
593027761 8784
593027761 2544
593027761 4898
593027761 544
593027761 54887
593027761 216564
593027761 5487
593027761 54656
593027761 553
593027761 221
Hi, Dharampal,
please a closer look at this point of the article above. It contains the example of the formula you need to create to solve your task.
Hope this helps.
hi
i need a function for the mentioned query
NAME SEGMENT TARGET
A R 2
A R 3
A R 2
B R 3
B BB 4
B R 2
A BB 1
A R 3
NAME A
NAME WISE TOTAL :
NAME WISE SEGMENT TOTAL :
Please suggest NAME WISE TOTAL Required and NAME with SEGMENT wise total required.
It is very very helpful for me.
i need to find name with address and amount and also date wise
main sheet is PT NAME with amount and date and ADDRESS DATA is second sheet with address and third sheet is required sheet with VLOOKUP FORMULA but i am not co relate with amount and date kindly help me for solving the problem.
I want to apply vlookup formula in excel sheet having pt. name and address and also i having data only pt. name how can match pt name with colour code?
I want to apply vlookup (or any) formula in excel sheet having pt. name and address and also i having data only pt. name how can match pt name with colour code?
09/11 10:18: Mast Devesh Aahuja Mast DEVESH AAHUJA Mast DEVESH AAHUJA 4 Y Dr. Asim Negi 44 tilak path khargone
13/12 12:48: Mast Dhananjay Magwani Mast DEVESH AAHUJA Mast DHANANJAY MAGWANI 15 Y Dr. Jitendra Pindoriya gram kampel teh. kampel
19/10 17:03: Mast Divyanshu Prajapat Mast DHANANJAY MAGWANI #N/A 45 Y Dr. Jitendra Thakur gram fangti teh. hatpipliya
12/10 20:55: Mast Gajendra Yadav Mast DHANANJAY MAGWANI #N/A 13 Y Dr. M.K Sharma gram mogawa teh. maheshwar
kindly help me.
Dear Svetlana Cheusheva,
Can I sent an excel Sheet with data to make Formula to calculating
the Data with criteria
regards
Hello Svetlana,
I'm looking to get the MAXIMUM of the multiple LOOKUP VALUES which are scattered and which are in different worksheets.
I tried with vlookup and index / match but could not do.
Example:
The answer when I try to vlookup for A0600 should be 1700 and not 1692 from the two lines given below:
A0600 23099090 XXXX 1692.00
A0600 23099090 XXXX 1700.00
Thank you so much in advance for your help!
Thiyagu.
I need some help. I have several tabs in an excel file with amounts per person (i.e. John $35, Kathy $40). Each person has a separate identifier (ID). I need to add all of John's amounts into one spreadsheet, all of Kathy's, etc. John may be in tab 1 and on tab 2 but not on tab 3. I think I need to use some vlookup combined with a sum but I can't figure out what to use. Can you please help me?
Thank you!
Country 2011 2012 2013 2014 2015
Japan 1653 1232 5319 9230 7647
Ukraine 5582 7685 7706 3723 6181
Japan 7330 5872 6723 6133 3228
Poland 4063 4337 4916 7608 4451
Germany 2330 3906 4673 1276 1592
Ukraine 7396 1752 8262 9823 9164
Germany 6690 5303 5155 4422 7661
Poland 1170 5878 6089 2398 3338
This is the date, now, for example, I want to use sumifs formula to calculate the sum of sales for Japan after 2012. Can you help? I tried the formula "=SUMIFS(B2:F9;A2:A9;G3;B1:F1;">"&G4)" but it did not work.
Hi,
Anyone can help for the formula of sumif. i got a total sum figure like 206.61.
Can the sumif formula find out from the column which of the value add on together is equal to 206.61.
Thank you.