I recently came to know about Ctrl+Shift+Enter array formulas of Excel and currently still learning them. Coming to my problem,
SheetA:
Product Code
S1 19875
S2 19834
S1 13575
S1 35675
S2 47875
SheetB:
Code Indent
19875 40
19834 15
13575 22
35675 25
47875 20
I need to do Sum of all Indents of a given Product name.
Eg: I need S1's Total Indent,
I can achieve this through VBA, but I'm wondering if this is possible within excel functions using CSE/Array formulas.
Edit:
I don't have values in Sheet2 in the same order of Sheet1.. They are completely random. My SheetB will be something randomly like following:
SheetB:
Code Indent
19834 40
19875 15
47875 22
13575 25
35675 20
{=SUM(NOT(ISNA(MATCH((($A$2:$A$6="S1")*(B2:B6)),Sheet2!$A$2:$A$6,FALSE)))*(Sheet2!$B$2:$B$6))}
The first argument of the MATCH resolves to
{19875;0;13575;35675;0}
The MATCH resolves to
{1;#N/A;3;4;#N/A}
You'll have to make sure you don't have zeros in SheetB. The NOT ISNA turns those into TRUEs and FALSEs and resolves to
{TRUE;FALSE;TRUE;TRUE;FALSE}
And the final SUM looks like this
=SUM({TRUE;FALSE;TRUE;TRUE;FALSE}*{40;15;22;25;20})
Update
I can't figure out a single-array solution when the lists are in a different order. My attempts with OFFSET and TRANSPOSE either gave the wrong answer or crashed Excel. If you can stand using a helper column, you could put this formula in third column of your first sheet
=VLOOKUP(B2,Sheet2!$A$2:$B$6,2,FALSE)
and then use this array formula to sum them up
{=SUM(($A$2:$A$6=A2)*($C$2:$C$6))}