SumIF for a specific item code

[usama5788]

Hi please see attachement.

Can someone please assist in obtaining a excel formula or VBA code that will provide the final result in the highlighted cells. I need to make sure that the formula is dictated by the Account Number (4424-9999) and TO Number. Since I would need to adjust this formula later for different account numbers.


Need a formula for the highlighted cell in which it looks for cell B1 then get SumIF of the TO Number from Sheet 1. Also I have to make sure the TO608 is sum total is 0 since the amounts are Debit and Credit.

[CK76]

You will need to provide bit more sample than provided (i.e. TO number other than 4424-9999) so that we can clearly see the structure of your data.

For ease of calculation and analysis, I'd recommend using PowerQuery/Get & Transform to restructure the data layout.
Then it's easy process to load it to data model and report on it using Pivot table.

Your MS-Office ver is 210. I assume it's 2010? PowerQuery is a free add-in for that version.
In my opinion, it's one of most powerful tool in Excel's arsenal and is under utilized.

[usama5788]

Hi,

Please see attached more information than one account. As you can see in Sheet2 I need to have sumif of all the Account Numbers (4424-9999/3647-5874/5746-0000) from Sheet 1 as you can see. The highlighted information is the final result I am looking for.

[CK76]

Here you go.

See attached. You can follow the steps in PowerQuery editor.

[usama5788]

Hi,

Thank you for the help. With the attached power query file. I just want to make one small change. I want to remove column G which is a duplicate of F so I need to remove that and re-run the power query.

I have tried to edit it but my query keeps on breaking. I tried to edit the source but it wont let me either. Can you please assit?

[CK76]

You can either delete it from source table or just do it in PQ editor.

If you remove from source table. You will need to go into PQ Editor and ensure each steps that refer to department_1 column are changed (mostly Changed Types and Reordered Columns steps).

See attached.