HOW TO COUPLE VLOOKUP & SUMIF

[SSJ]

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

 

[Ron Coderre]

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)
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

 

[JP]

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

 

[Eli]

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




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