Stock Balance (Pivot Table and/or Formula Solution Required)

[john555]

I am seeking help for finding stock balance for the attached sample file. I have an excel file named Sample . I like to find balance of the each item by
Balance = Opening balance +Cycle given to fitters - Cycle taken from fitters - Cycle take from fitters and kept in Godown.

[AliGW]

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[Glenn Kennedy]

Your attachment violates rule 10.

https://www.excelforum.com/forum-rul...rum-rules.html

Please remove the link in Sheet 3 and repost it.

NO HELP TO BE OFFERED until the link is removed.

[john555]

Yes Madam Still I am using Excel - 2007 .

[AliGW]

Read and respond to post #3.

[john555]

Link Removed.


Sorry for that

[john555]

I have responded Question No.3

[Glenn Kennedy]

Thanks for that... open for business again.

[Glenn Kennedy]

Maybe:

=SUMPRODUCT(--(B3:E26=N3)*N(C3:F26))-SUMPRODUCT(--(H3:K26=N3)*N(I3:L26))

it >>>might<<< be an array fromula (CTRL-SHIFT-ENTER) in your rather elderly Excel product

[john555]

Thanks

But balance should come

Opening Balance - 10
Cycle given to fitters - 15

Total = 25

Taken from fitters (-) 5
==========
20
Taken from fitters and
Kept in Godown (-) 5
============
Balance = 15

How to correct the formula

[Glenn Kennedy]

That would be because I am an idiot.

=SUMPRODUCT(--(B3:E26=N3)*N(+C3:F26))-SUMPRODUCT(--(H3:K26=N3)*N(+I3:L26))

[john555]

Thank you for your help.

[john555]

Can I ask one more question regarding this same subject.

If I have 3 column , Stock, Sold, Balance then what will be the formula

[JeteMc]

The formula in column I needs to have the row references locked, as in:

Please Login or Register  to view this content.

Let us know if you have any questions.