# Index Match sumif Formula Required

#### mianmazher

##### Active Member
Book1
ABCDE
1Order No.Party NameStationReceiving DateTrucks
21000ANY20-02-041
31001BMI20-02-041
41002CIL21-02-044
51005DNJ21-02-042
61005AENY21-02-041
81007GTRONTO22-02-041
91008HMEXICO22-02-041
Book1
ABCDE
21000ANY21-02-041
31002CIL22-02-042
41005DNJ22-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
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Andrew Poulsom

##### MrExcel MVP
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.

#### mianmazher

##### Active Member
Dear Andrew

I need the formula approach to do

Regards

Mian Mazher

#### fairwinds

##### MrExcel MVP
Hi,

Take a look at this:

But I'm sure it will be heavy witf these formulas for 1000 rows
Book1
ABCDEFGHIJKLMNOP
21000ANY2004-02-2011000ANY2004-02-21131001BMI2004-02-201
31001BMI2004-02-2011002CIL2004-02-22241002CIL2004-02-212
41002CIL2004-02-2141005DNJ2004-02-22261005AENY2004-02-211
61005AENY2004-02-2111007GTRONTO2004-02-231####
81007GTRONTO2004-02-221####
91008HMEXICO2004-02-221####
Sheet4

#### TheBuGz

##### Spammer

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

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
Oops!! actually i saved this made it and without seeig the post Jumped to the Sorry <== This is me

#### mianmazher

##### Active Member
Hi all

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

Regards

Mian Mazher

#### mianmazher

##### Active Member

Regards

Mian Mazher

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,038
Messages
5,856,973
Members
431,843
Latest member
Malahhai

### 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.

### Which adblocker are you using?

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

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