SUMIFS and multiple parameters across rows and columns

**george_k** · 11-02-2012, 12:48 PM

Hi,

I previously asked a question on matching criteria across both rows and columns here:

http://www.excelforum.com/excel-gene...d-columns.html

The solution provided was a SUMPRODUCT function which works, however, given the size of my data ( set it's very slow so any help to optimize the speed would be a big help.

I read about the speed advantages of using a SUMIFS function and was wondering if it's possible to populate the table in the "Summary Page" sheet with the data from the "Data Page" sheet.

The part that's giving me trouble how to match criteria across both rows and columns.

Any help would be kindly appreciated.

Thank you,

George

Book1.xlsx

**martindwilson** · 11-02-2012, 01:35 PM

you show excel 2003 sumifs is not in excel 2003

**george_k** · 11-02-2012, 01:39 PM

My appologies, I forgot to update my profile I'm on Excel 2010

**icestationzbra** · 11-02-2012, 02:09 PM

this is why it is a good idea to keep your profile updated.

see if the attached helps:

**george_k** · 11-02-2012, 02:56 PM

Thank you again icestationzbra.

The improvement in calculation time by switch to the COUNTIFS instead of SUMPRODUCT is HUGE!

A recalculation of my entire sheet used to take between 15-20 minutes and now with the modifications it takes ~1 minutes...this is a 15-20x improvement!