Sumif (maybe index) question(s)

[Kwgriffi]

attached is an example file.

my primary question is how to do a sum if if there are two criteria met (without having to concatenate cells in an additional column)

secondarily, is there a way to make dynamic formulas to a reference cell so i dont have to find and replace hundreds of columns off of my much larger data set

 

[Hui]

For this data layout I'd recommend using Sumproduct
eg:
Cell F8: =SUMPRODUCT((Summary!$C$4:$K$25)*(Summary!$A$4:$A$25=F$5)*(Summary!$B$4:$B$25=$A8)*(Summary!$C$2:$K$2=F$6)*(Summary!$C$3:$K$3="Sales"))

You can copy this to other locations on the Data worksheet and adjust references as applicable

I'm not sure if Sales should refer to the data worksheet Row 7

You can read about how this works here:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[azumi]

Hope this helps