Reputation: 11

sum multiple columns with sumif function in excel

I would like to sum multiple columns with sumif function column by colum.

For example. Criteria range is "Column A" Criteria is "Aplle" Sum range is "C1:G100"

I dont want to use SUMIF(…) + SUMIF(…) + SUMIF(…) way since I have many columnns.

Thank you

I have tried offset function but didn't work.

Example

Upvotes: 0

Answers (2)

Reputation: 6177

What about this formula. This sums all cells in all columns which row's A cell equal to condition in cell I1. Result in J1.

This formula is in column H

=IF(A1=$I$1,SUM(INDIRECT("C"&ROW()&":G"&ROW())),0)

Result formula in J1.

=SUM(H2:H100)

=SUM(B1:G100*($I$1=A1:A100))

where in I1 cell is the value to search to sum. And instead B1:G100 sequence from B1:B100

Upvotes: 0

Reputation: 71

2 ways to get sum of columns from the Col A as criteria.

Use IF condition to filter the numbers, then wrap it with "SUM" to get the total.

=SUM(IF(A2:A21="Apple",C2:G21))

2.Use FILTER Output the array of cells from Columns C to G then SUM the arrays.

=SUM(FILTER(C2:G21,A2:A21="Apple"))

Edit:

Based on the image, you can freeze the first column then drag to start the SUM from the first column

=SUM(IF($A$2:$A$25=$B28,$C$2:C25))

dragged to the final column:

=SUM(IF($A$2:$A$25=$B28,$C$2:G25))

The above code will match the CRITERIA then will proceed adding the values from $C:C to $C:G

Upvotes: 0