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)

Answer 2

Maybe (difficult to tell from the question):

=sumif(A:A,"=x",B:B)

Answer 3

Another answer could be that you create a pivot table of the database. Insert>Pivot Table>Select your table range. Then from there, you would put the column heading you wanted sorted into the row criteria, and then sum the x1's and y'1s.

Answer 4

I am assuming you want to obtain subtotals for each column and each letter, as in the figure below.

If so, enter in B12 the formula

=SUMPRODUCT(($A$2:$A$8=$A12)*(B$2:B$8))

Copy and paste into B12:C14. It is easy to adapt to slightly different arrangements.

Answer 5

I apologize if my question was vague. This is my first time working with if and sumif formulas in Excel.

This is what I was looking for: =sumif(A:A,"=x",B:B).

Thank you Pnuts.