Excel Magic Trick 1036: SUM Rectangular Table, 1 condition Row Headers & Or Criteria Column Headers

Conditional array operation to add with a single criteria in row headers and Or Criteria in Column Headers.
See how to sum (add) from rectangular table of numbers matching single condition from row headers and matching Or Criteria from column headers:
1. Using: MATCH function, ISNUMBER function, Array Comparative Operation Calculation, Array Multiplication Operation Calculation, and SUMPRODUCT function.

Add: Match Condition From Row Headers & Or Criteria From Column Headers

[excelisfun]

Glad you like the video! Thank you for your support by buying the book!

[DamianMega]

Sheer brilliance. I adapted this methodology to use SUMPRODUCT to add up all the numeric values in each column where the header matched the parameter set. The part I was missing was making both parts of the formula the same array size. THANK YOU a million times over.

[ExcelStrategy]

Amazing complex concepts in this video ! The MATCH with a column as the second argument obtaining a reference bunch of TRUE and FALSE, then Matrix multiplication to obtain a new reference array and them multiply that array for the actual array to sum the values that we want !!! Not to mention the mixed cell references !!! Superb !

[excelisfun]

Great! I am glad that you found it useful! Excel is fun when you can put it all together!

[nteil]

Beautiful - Thanks for the help! Will save me many hours over the next months!

[MySpreadsheetLab]

Another classic solution Mike! ...I finally got your control shift enter book from amazon! GREAT BOOK!!

[excelisfun]

Yes, this method should be dramatically shorter and easier to maintain!

[livinindubai]

Wow. I didn't understand much but knowing that video would help me lot!

[afterpartners]

Awesome thank you! Great Explanation helped tremendously! Also note it you have a #NUM Error the formula will not work. You must remove any of those errors prior to using this. Took me a moment to figure that out.

[Iexecutethings]

Do you have a specific function activated or installed?

When I do the F9 with the Match function, it comes up slightly

[brandi.bw_]

I have 5 worksheets, same headers, different order. I need to find out how to highlight duplicate values on one header column. I don't know how to select a range across the entire workbook on only columns with a header titled "mapped code". I can't assign billable codes more than one against a contract. A contract can have multiple worksheets I'm reviewing that I will eventually merge together. I want to validate i'm not assigning duplicate codes before I merge everything together.