Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
Hello!
1) I have two tabs, namely, Tab 1 and Tab 2 2) Tab 1 has two headings, namely, "Work Order Number" and "Total". Underneath would be a list of work orders. 3) Tab 2 has two three headings, namely, "Date", "Work Order Number" and "Amount". This is a list of work order numbers in random with dates and amounts. The amounts could be positive as well as negative. I am look for a formula under the heading "Total" in the Tab 1, which will do the following: a) The formula will look at the 'Work Order Number' beside it and then try to locate that work order number in Tab 2. b) If it finds that work order number in Tab 2 then it will sum all the various amounts showing up against the work order. c) If it does not find the work order then either it can show zero or blank. Thanks for the help ion advance SJ |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
If Work Order Number and Total are in cells A1:B1 on Tab 1
and Date, Work Order Number and Amount are in cells A1:C1 on Tab 2 Try this..on Tab 1: B2: =SUMIF('Tab 2'!B:B,'Tab 1'!A2,'Tab 2'!C:C) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "SSJ" wrote in message ... Hello! 1) I have two tabs, namely, Tab 1 and Tab 2 2) Tab 1 has two headings, namely, "Work Order Number" and "Total". Underneath would be a list of work orders. 3) Tab 2 has two three headings, namely, "Date", "Work Order Number" and "Amount". This is a list of work order numbers in random with dates and amounts. The amounts could be positive as well as negative. I am look for a formula under the heading "Total" in the Tab 1, which will do the following: a) The formula will look at the 'Work Order Number' beside it and then try to locate that work order number in Tab 2. b) If it finds that work order number in Tab 2 then it will sum all the various amounts showing up against the work order. c) If it does not find the work order then either it can show zero or blank. Thanks for the help ion advance SJ |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
Assuming your data was in A1:B100 on Tab 1, and A1:C100 on Tab 2, this
formula would go on Tab 1 in the totals column. Fill down as needed, it will draw the work order number as appropriate. =SUMIF('Tab 2'!B2:B100,'Tab 1'!A2,'Tab 2'!C2:C100) HTH, JP On Feb 27, 9:18*am, "SSJ" wrote: Hello! 1) I have two tabs, namely, Tab 1 and Tab 2 2) Tab 1 has two headings, namely, "Work Order Number" and "Total". Underneath would be a list of work orders. 3) Tab 2 has two three headings, namely, "Date", "Work Order Number" and "Amount". This is a list of work order numbers in random with dates and amounts. The amounts could be positive as well as negative. I am look for a formula under the heading "Total" in the Tab 1, which will do the following: a) The formula will look at the 'Work Order Number' beside it and then try to locate that work order number in Tab 2. b) If it finds that work order number in Tab 2 then it will sum all the various amounts showing up against the work order. c) If it does not find the work order then either it can show zero or blank. Thanks for the help ion advance SJ |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
Hello,
Just create a Pivot Table from Tab2, then drop the WorkOrderNumber on the rows and Total on the columns. The Pivot will group all the happenings of each WorkOrderNumber into one item and will aggregate their Total. Eli Prism - Enhance your Excel with Business Intelligence power www.sisense.com "SSJ" wrote in message ... Hello! 1) I have two tabs, namely, Tab 1 and Tab 2 2) Tab 1 has two headings, namely, "Work Order Number" and "Total". Underneath would be a list of work orders. 3) Tab 2 has two three headings, namely, "Date", "Work Order Number" and "Amount". This is a list of work order numbers in random with dates and amounts. The amounts could be positive as well as negative. I am look for a formula under the heading "Total" in the Tab 1, which will do the following: a) The formula will look at the 'Work Order Number' beside it and then try to locate that work order number in Tab 2. b) If it finds that work order number in Tab 2 then it will sum all the various amounts showing up against the work order. c) If it does not find the work order then either it can show zero or blank. Thanks for the help ion advance SJ |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
Thank you Ron & JP.
I will try them out and let you both know. However, I missed something to mention. Which is that work orders in Tab 1 are grouped. For example a group of project managment work orders with sub total, a group of engineering work orders with sub total and so on and so forth. So there are breaks. However, let me try the suggestion out and then I will report back. Thanks SJ "SSJ" wrote in message ... Hello! 1) I have two tabs, namely, Tab 1 and Tab 2 2) Tab 1 has two headings, namely, "Work Order Number" and "Total". Underneath would be a list of work orders. 3) Tab 2 has two three headings, namely, "Date", "Work Order Number" and "Amount". This is a list of work order numbers in random with dates and amounts. The amounts could be positive as well as negative. I am look for a formula under the heading "Total" in the Tab 1, which will do the following: a) The formula will look at the 'Work Order Number' beside it and then try to locate that work order number in Tab 2. b) If it finds that work order number in Tab 2 then it will sum all the various amounts showing up against the work order. c) If it does not find the work order then either it can show zero or blank. Thanks for the help ion advance SJ |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
HOW TO COUPLE VLOOKUP & SUMIF
It shouldn't matter as long as you place the formula on Tab 1 right
next to the Work Order Number in column A. Just skip the subtotal and blank rows. HTH, JP On Feb 27, 10:30*am, "SSJ" wrote: Thank you Ron & JP. I will try them out and let you both know. However, I missed something to mention. Which is that work orders in Tab 1 are grouped. For example a group of project managment work orders with sub total, a group of engineering work orders with sub total and so on and so forth. So there are breaks. However, let me try the suggestion out and then I will report back. Thanks SJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Couple of Questions Need Help Please..... | Excel Discussion (Misc queries) | |||
A couple of questions | Excel Discussion (Misc queries) | |||
Couple of vlookup queries | Excel Worksheet Functions | |||
Couple more questions... | New Users to Excel | |||
There are a couple of parts to this ???...Thank you for the help. | Excel Worksheet Functions |