excel Formula to sum values in a column based on another value(txt) in any column

Question

I'm trying to write an If or SumIf to calculate(sum) totals for x , y and z individually. I could use a simple sum formula but these are thousands of columns and x and y and z are populated randomly. I tried using a range by sorting colA but its a temporary solution and not what I am looking for.

I need something like:

If  COL A has 'X' then add values corresponding to X in COL B

example:

COLA   COLB  ....... colx
x      1         
x      2                
x      1
y      3
x      3
z      3
x      4                 

I tried looking up other answers for similar questions but could not find the right one that works for me.

Answer 1

If you want to sum multiple columns based on a value in a single column you could use SUMPRODUCT like this

=SUMPRODUCT((A2:A100="x")*B2:X100)

There can't be text in the sum range, B2:X100, otherwise you get an error - if you want to allow text in that range use this version:

=SUMPRODUCT((A2:A100="x")*ISNUMBER(B2:X100),B2:X100)