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

[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