Sum All Rows And Columns In A Range Where Criteria Matches Across Rows And Columns

[normonster]

Thank you in advance for any help you might lend in answering this question.

I believe I have this formula entered correctly, but I am getting an error value. Any tips on how I might improve it? I am sure I've used Sumifs in similar situations before with good results.

1650

[normonster]

Sorry, this example only contains two condition ranges, one for row labels and one for column headers (vertical and horizontal).

[Excel Fox]

normonster, if the States and Brands do not repeat itself, then you can use this formula (in K2, based on the snapshot)

=INDEX($B$2:$D$5,MATCH(I2,$A$2:$A$5,),MATCH(J2,$B$ 1:$D$1,))

However, if the States and/or Brands are not unique, ie, if there are possibly the same State repeating itself as you go down the row, or Brands repeat itself across the columns, then you can use an array formula (again, in K2, according to the picture you posted)

=SUM(IFERROR(N(INDIRECT(IF($A$2:$A$12=I2,"R"&ROW($A$2:$A$12),0)&IF($B$1:$F$1=J2,"C"&COLUMN($B$1:$F$1),0),FALSE)),0))

For a sample, please find attached a workbook using both the solutions

[normonster]

Thank you very much Fox. Your response is very much appreciated.

I don't mean to resist your solution, but the SumifS function seems far more simple. Are you just partial to the INDEX function or do you believe that the SumifS function will not work with multiple conditions when they are spread over both Rows and Columns? I swear I have used the SumifS function in instances just like this before, but I could be wrong. Can you offer me any clarification in terms of the suitability of the SumifS function in this circumstance?

Again, thank you so much.

[Excel Fox]

Sumifs using multiple criterias with criteria ranges spread across - Microsoft Community (http://answers.microsoft.com/en-us/office/forum/office_2007-excel/sumifs-using-multiple-criterias-with-criteria/01fb1ad5-adb2-4d61-a126-d126927fbfee)

which translate to =SUMIFS(INDEX($B$2:$D$5,,MATCH(J2,$B$1:$D$1,)),$A$ 2:$A$5,I2) in your case

Is this what you are looking for? Great if yes.

The problem there is that the duplicates are only in the column, and not across the row also. If there are duplicates across the column and row, as in my example above, then I (seriously) doubt SUMIFS alone can handle it.

Another way this can be done is using SUMPRODUCT. The advantage here is that it works equally well when there are duplicate column and row headers as when there are unique.

=SUMPRODUCT(($A$2:$A$12=I2)*($B$1:$F$1=J2),$B$2:$F $12)

[normonster]

Thank you Sir. I guess Sumproduct is the way to go and it is indeed a great function. I appreciate your help. ;)