Determine inventory stock on hand

[Dolarnesia]

I have a problem managing stock, I want to use an excel formula that can be dragged and dropped down, so the results will be faster,
please to the experts here to solve my problem, thank you very much for those who have helped answer.

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "need help to find excel formula" is not a good title.

I have updated it for you this time because you are a new member. --6StringJazzer

I opened your file and it's just a column with product names and another column with colors. Each pair of product/color is unique. You need to tell us what result you want.

[Dolarnesia]

the result i want in another sheet sir, in input sheet, please help me

[Sam Capricci]

This would be one way, a bunch of sumifs strung together ...

=SUMIFS(Data!$D:$D,Data!$A:$A,Input!$A2,Data!$B:$B,Input!$B2)+SUMIFS(Data!$H:$H,Data!$A:$A,Input!$A2,Data!$F:$F,Input!$B2)+SUMIFS(Data!$L:$L,Data!$A:$A,Input!$A2,Data!$J:$J,Input!$B2)+SUMIFS(Data!$P:$P,Data!$A:$A,Input!$A2,Data!$N:$N,Input!$B2)+SUMIFS(Data!$T:$T,Data!$A:$A,Input!$A2,Data!$R:$R,Input!$B2)+SUMIFS(Data!$X:$X,Data!$A:$A,Input!$A2,Data!$V:$V,Input!$B2)+SUMIFS(Data!$AB:$AB,Data!$A:$A,Input!$A2,Data!$Z:$Z,Input!$B2)+SUMIFS(Data!$AF:$AF,Data!$A:$A,Input!$A2,Data!$AD:$AD,Input!$B2)+SUMIFS(Data!$AJ:$AJ,Data!$A:$A,Input!$A2,Data!$AH:$AH,Input!$B2)+SUMIFS(Data!$AN:$AN,Data!$A:$A,Input!$A2,Data!$AL:$AL,Input!$B2)

[Dolarnesia]

thank you for the answer sir, but my original data has thousands of products, I've tried using "ifs", but the resulting formula is too long and the excel formula has a character input limit.

[JeteMc]

Hello Dolarnesia and Welcome to Excel Forum.
The following is an array entered formula which is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Please Login or Register  to view this content.

Paste the formula into cell C2 on the Input sheet and then activate as described above. After activation double click the fill handle to copy the formula down to cell C90.
Let us know if you have any questions.

[torachan]

Small macro on button on sheet 3 (STOCK).
should accomodate your 'thousands' of products.
torachan.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

ARRAY formula in C2 copied down

=SUM(IFERROR((INDEX(Data!$B$1:$AL$14,MATCH(Input!$A2,Data!$A$1:$A$14,0),)=$B2)*INDEX(Data!$D$1:$AN$14,MATCH(Input!$A2,Data!$A$1:$A$14,0),),0))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.