Help Required - Inventory Control Sheet

[Aaditya Hamine]

Hi all Gurus !

Need a small help in developing a logic for Inventory sheet.

I have uploaded file wherein you can get the details.

Await your valuable feedback.

Thanks,
Aaditya

 

[Hui]

A3: =SUMIF($E$8:$E$3952,A2,$G$8:$G$3952)/10000000
Copy across

H4: =SUMIFS(G8:G3952,E8:E3952,A2)

J4: =SUMIFS($G$8:$G$3952,$E$8:$E$3952,$A$2,$H$8:$H$3952,J3)
Copy across

N4: =SUMIFS($G$8:$G$19,$E$8:$E$19,$A$2,$I$8:$I$19,"<=1")
Copy across and adjust

 

[Aaditya Hamine]

Hello Sir !
Thanks for your reply. I tried using above logic , but I feel i could not convey my requirement in a proper way .
What I desire is values to be changed automatically when I apply filter at 'Buyer" - Column 'E'.
I mean if a choose buyer AH -

1. It should give me total inventory in Column H as - 21511252+10479164 = 31990417
2. In column N4 - it should give value - 21511252 ( as this has o to 1 aging)
3. In column P4 - it should give value - 10479164 ( as this has 2+ aging)

Same would be logic if I choose 'Aging' -
1. If I choose aging as 1 - I would get the sum of all the products (Cumulative value) in A3 and across . It will give me , for aging of 1 - how is the value bi-furcation 'Buyer Wise'

I feel subtotal +sumif would help in this case - but I really don't know how to write this logic.

Look forward to your help.

Thanks in advance.

Aaditya

 

[Deepak]

Check this...

 

[Hui]

In addition to Deepak's solutions
in J4: =SUMPRODUCT(SUBTOTAL(109,OFFSET($G$8,ROW($G$8:$G$19)-8,,1))*($H$8:$H$19=J$3))
Copy across to L4