Excel Sumifs Formula to 3 Columns

1
votes

Hey I have SUMIFS formula that I built to 3 columns separately (each month data is in a different column). How can I make this formula shorter? Any ideas? I have 2 criterias and I want to sum everything that is right in each of the 3 columns. I want somehow to get all the data from column U to W.

=SUMIFS(OldConversion!U:U,OldConversion!H:H,Level!$R$1,OldConversion!A:A,Level!A2) + SUMIFS(OldConversion!V:V,OldConversion!H:H,Level!$R$1,OldConversion!A:A,Level!A2) + SUMIFS(OldConversion!W:W,OldConversion!H:H,Level!$R$1,OldConversion!A:A,Level!A2)

1
excelexcel-formula

1 Answers

1
votes

You can use SUMPRODUCT like below:

=SUMPRODUCT(OldConversion!U:W*(OldConversion!H:H=Level!$R$1)*(OldConversion!A:A=Level!A2))

Note Avoid complete column references as they will add calculation overhead.