# Using sumifs to sum multiple criteria, when the criteria changes down a column

1. ## Using sumifs to sum multiple criteria, when the criteria changes down a column

Hi team ,

Hoping for some help!

Looking for a way to sum data using sumifs ( maybe) when you have multiple criteria, but the criteria changes through the data set. Its easy to write the formula for criteria that stays constant, but is there an easier way than manually changing the criteria ?

My example in the attachment shows A- D , each contain a number of colours.

To Sum say A and white =sumifs(Column C, Column A, "A",Column B , "White) . Would use cell references for the criteria range usually , but have just used the columns in this example.

But as I go down the list I want sums for A & Red, A & Blue, B & White, B & Red etc.

What is the best way to do this please.

Thanks!  Register To Reply

2. ## Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

Try this =SUMIFS(\$C\$1:C2,\$A\$1:A2,A2,\$B\$1:B2,B2)
Or for a better view,=IF(AND(A3=A2,B3=B2),SUMIFS(\$C\$1:C3,\$A\$1:A3,A3,\$B\$1:B3,B3),"")  Register To Reply

3. ## Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

The simplest way, which avoids formula altogether is a Pivot Table and a couple fo Slicers to filter your data.

Using a formula it would be easier to add a helper column to your table and use a SUMIFS. I've shown a couple of examples

See attached  Register To Reply

4. ## Re: Using sumifs to sum multiple criteria, when the criteria changes down a column

Cheers Richard,  Register To Reply