SUMIFS with INDEX and MATCH

[lewis_blackburn]

Could someone please help me piece together the correct formula to sum up values in a cell range that meet my criteria? Consider the following range...

<TABLE><TBODY><TR><TD></TD><TD>Jan</TD><TD>Feb</TD><TD>Mar</TD></TR><TR><TD>VTSMX</TD><TD>100</TD><TD>300</TD><TD>200</TD></TR><TR><TD>VISVX</TD><TD>110</TD><TD>330</TD><TD>220</TD></TR><TR><TD>VBMFX</TD><TD>140</TD><TD>360</TD><TD>250</TD></TR></TBODY></TABLE>

...and assume that there is a named range called "UsStockFunds" that contains "VTSMX" and "VISVX". What I want to do it sum up all values in the range for January where the fund name is in the "UsStockFunds" named range. Here is my first attempt at the formula...

=SUMPRODUCT(SUMIFS(B2:D4, B1:D1, "Jan", A2:A4, UsStockFunds))

...which obviously didn't work. Can anyone figure out the correct formula to sum these values? The correct sum is 210.

Thanks!

 

[pgc01]

Hi

Try:

=SUM(IF(B1:D1="Jan",IF(ISNUMBER(MATCH(A2:A4,UsStockFunds,0)),B2:D4)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

 

[jim may]

This worked for me...

=SUM(INDEX(B2:D4,MATCH("VTSMX",$A2:$A4,0),MATCH("Jan",$B$1:$D$1,0)))+SUM(INDEX(B2:D4,MATCH("VISVX",$A2:$A4,0),MATCH("Jan",$B$1:$D$1,0)))