Sum an index value based on input from several columns

[alcatrax]

Hi,

I have the following simplified excel data:

column1 column2 column3 column4
row2 20 3 108 66
row3 17 54 99 208
row4 15 17 5 57
row5 9 98 44 3

I am looking for a formula that first matches the row number based on a given value (for example '3') and returns the value of the first column. Next, it will sum all the matches found. In this example with 3. It will find '3' in columns 2 and 4 and returns 20 + 9 = 29. Is that possible?

So far I have: =SUM(INDEX(H2:AF16,MATCH(M25;Stage2;0);1))

But this will only look in the range set by Stage2 (which is only one column). Extending this range to multiple columns does not work.

 

[WinteE]

Try this :

=SUMPRODUCT((B2:D5=3)*(A2:A5))

 

[Sektor]

UDF. Parameters description:
1. Rng - range with digits.
2. DigitToFind - self-describing.

Type in any cel, for example: =FirstDigits(A2:D6,3)

[COLOR="Blue"]Function[/COLOR] FirstDigits(Rng [COLOR="Blue"]As[/COLOR] Range, DigitToFind [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] Variant, res [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]
    
    Application.Volatile
    arr = Rng
    
    [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr, 1)
        [COLOR="Blue"]For[/COLOR] j = 1 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr, 2)
            [COLOR="Blue"]If[/COLOR] arr(i, j) = DigitToFind [COLOR="Blue"]Then[/COLOR]
                res = res + arr(i, 1)
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]Next[/COLOR]
    
    FirstDigits = res

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[alcatrax]

Thanks for the reply. The sumproduct did it! Apparently no need for match, index, offset etc etc. Just a simple sumproduct will do......*sigh*

Thanks again.

As for the VBA function: that is worth studying for future reference!