Index Match sumif Formula Required

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
Book1
ABCDE
1Order No.Party NameStationReceiving DateTrucks
21000ANY20-02-041
31001BMI20-02-041
41002CIL21-02-044
51005DNJ21-02-042
61005AENY21-02-041
71006FCANADA22-02-045
81007GTRONTO22-02-041
91008HMEXICO22-02-041
Received
Book1
ABCDE
1Order No.Party NameStationReceived dateTrucks
21000ANY21-02-041
31002CIL22-02-042
41005DNJ22-02-042
51006FCANADA23-02-042
61007GTRONTO23-02-041
71008HMEXICO23-02-041
Despatched
Book1
ABCDE
1Order No.Party NameStationReceiving DateTrucks
21001BMI20-02-041
31002CIL21-02-042
41005AENY21-02-041
51006FCANADA22-02-043
Balance



I want the balance Order sheet to be claculated automatically

the list should be sorted automatically and should not show the items that have been despatched

I have a long list of datain the received sheet abt 1000 records

I need the sheet Balnce to be claculated automatically
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why don't you sum the despatches for each order on your Received sheet and calculate the balance? Then you could AutoFilter the balance column for non zero to get a list of what's outstanding.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Take a look at this:

But I'm sure it will be heavy witf these formulas for 1000 rows
Book1
ABCDEFGHIJKLMNOP
1OrderNo.PartyNameStationReceivingDateTrucksOrderNo.PartyNameStationReceiveddateTrucksOrderNo.PartyNameStationReceivingDateTrucks
21000ANY2004-02-2011000ANY2004-02-21131001BMI2004-02-201
31001BMI2004-02-2011002CIL2004-02-22241002CIL2004-02-212
41002CIL2004-02-2141005DNJ2004-02-22261005AENY2004-02-211
51005DNJ2004-02-2121006FCANADA2004-02-23271006FCANADA2004-02-223
61005AENY2004-02-2111007GTRONTO2004-02-231####     
71006FCANADA2004-02-2251008HMEXICO2004-02-231####     
81007GTRONTO2004-02-221####     
91008HMEXICO2004-02-221####     
Sheet4
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367

ADVERTISEMENT

well see if My approaches helps u
Mazher (order).xls
ABCDEFG
1OrderPartyStationReceivingTotalDespatchedBalance
2NoNameDateTrucksTrucksTrucks
31000ANY20.02.04110
41001BMI20.02.04101
51002CIL21.02.04422
61005DNJ21.02.04220
71005AENY21.02.04110
81006FCANADA22.02.04523
91007GTRONTO22.02.04101
101008HMEXICO22.02.04101
Sheet3



Well in the Balance Row U can have some Formula or Apply Filter such that the cell with the Value zero is hidden. I am not a VBA guy nir an expert may be some expert can modify this a lil bit to give u the desired results.
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
well in the Balance Column U can apply Filter Does Not contain 0 and thats it it will look after to ur Requirement.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

TheBuGz said:
well in the Balance Column U can apply Filter Does Not contain 0 and thats it it will look after to ur Requirement.

Isn't that what I suggested and was rejected?
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
Oops!! actually i saved this made it and without seeig the post Jumped to the Sorry :oops: <== This is me
 

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
Hi all

thanx for replying

My data is on two different sheets and I want the summary of the Balance orders in the Balance Sheet


Regards

Mian Mazher
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top