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

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

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

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

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

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