Summing With IF/INDEX/MATCH

Hi All,
I have an INDEX & MATCH looking up values, and retrieving a "-" in case of an error or in case of a zero. I am looking to SUM all values in the "2021" column with the A407 criteria. Can't seem to get it working with adding the SUM before the INDEX in the formula.

Please Login or Register  to view this content.

[/CODE]

Any help would be wonderful!

Thanks,

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Why not use number formatting to display 0 as - rather than change numeric zeroes to "-" ?

=IFERROR(INDEX('Inventory'!$1:$999999,MATCH($A407,'Inventory'!$A$1:$A$999999,0),MATCH("2021",'Inventory'!$1:$1,0)),0)

with number format #,##0;-#,##0;"-";@.

Anyway, summing. If you mean there could be several instances of the value of cell A407 in 'Inventory'!$A$1:$A$999999 (why not just use $A:$A?), and you want to sum all of the corresponding values in the 2021 column,

=SUMIFS(INDEX('Inventory'!$1:$999999,0,MATCH("2021",'Inventory'!$1:$1,0)),'Inventory'!$A$1:$A$999999,$A407)