Hello - I need a formula that will work in a matrix - I have been using SUMIFS to populate my worksheet. I need a non-array formula (too slow) that can handle multiple criteria that can pull both text and numbers. One criterion in the SUMIFS is a column and the corresponding array are rows (in bold) The reason for text is so I can conditional format this text onto my matrix if a certain item is found - currently, I am placing a numerical value to this (an unusually high number so the user will easily flag it), then a combination of conditional format and a nested IF formula if this value to found. The main part of the formula is below. =SUMIFS(DATA!$I:$I,DATA!$A:$A,'ALLOCATION'!$C13,DATA!$G:$G,'ALLOCATION!V$12)

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

SUMIFS-like Formula to grab text AND numbers

Hello -

I need a formula that will work in a matrix - I have been using SUMIFS to populate my worksheet.
I need a non-array formula (too slow) that can handle multiple criteria that can pull both text and numbers.
One criterion in the SUMIFS is a column and the corresponding array are rows (in bold)
The reason for text is so I can conditional format this text onto my matrix if a certain item is found - currently, I am placing a numerical value to this (an unusually high number so the user will easily flag it), then a combination of conditional format and a nested IF formula if this value to found.
The main part of the formula is below.

=SUMIFS(DATA!$I:$I,DATA!$A:$A,'ALLOCATION'!$C13,DATA!$G:$G,'ALLOCATION!V$12)