RE: MATCH and INDEX combination using multiple criteria?

[Tom Hutchins]

You can do it easily with SUMPRODUCT or with a SUM(IF( array formula. An
array formula must be entered by pressing CTRL+Shift+Enter and not just
Enter. If you do it correctly then Excel will put curly brackets around the
formula {}. You can't type these yourself. If you edit the formula you must
enter it again with CTRL+Shift+Enter.

The SUMPRODUCT formula:
=SUMPRODUCT(--(A1:A6="Joe"),--(B1:B6=7),C1:C6)

The SUM(IF( array formula:
{=SUM(IF((A1:A6="Joe")*(B1:B6=7),C1:C6,0))}

Bob Phillips explains =sumproduct() in more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

 

[ryguy7272]

I can think of a couple things:
With 'Joe' in F1 and 'Amount' in F2
F3 =INDEX(A1:C7,MATCH(F1,A1:A7,1),MATCH(F2,A1:C1,0))

Or...
=SUMPRODUCT(--(A1:A7="Joe"),--(B1:B7=7),C1:C7)

HTH,
Ryan--