excel formula to add cells in a single row based on criteria

Question

Let's say,

Sheet1:A1 = x

Sheet2 contents look like

x 3 3 4 
y 0 2 1

Is there an excel formula that could match Sheet1:A1 with Sheet2:A1 (basically the value 'x') and add the other cells in that row (3,3,4). The result (Sum='10') should get updated at Sheet1:C4 lets say.

I tried SUMIF but that shows the content of only one column due to the restriction that it can handle only the matchable array size. I know this can be achieved through VBA, but just wanted to know if a formula is available.

TIA!

Answer 1

This formula will do what you want:

=SUM(SUMIF(Sheet2!A:A,A1,INDIRECT("Sheet2!" & {"B:B","C:C","D:D"})))

It will iterate through the columns doing individual SUMIF() on each and then adding the results.

If you want more columns or different change the address in the array to the columns desired.

Answer 2

Try the following

=SUM(IF(Sheet2!A1:A99=A1,Sheet2!B1:D99,0))

Note that this is an array formula, so it must be entered using Ctrl+Shift+Enter.

What this formula does is converts any rows on Sheet2 without x in column A to zeros in B:D and then sums what is left.

Similarly, you could use

=SUMPRODUCT((Sheet2!A1:A99=A1)*Sheet2!B1:D99)

and you wouldn't have to enter it as an array formula.

Answer 3

For a non-volatile, non-array formula, try this

=SUM(INDEX(Sheet2!B:D,MATCH(Sheet1!A1,Sheet2!A:A,0),))