Extending a conditional index-match to sum across a range

0
votes

I have a conditional index-match 

=IF(ISBLANK(E3),0,INDEX(Positions!$F$2:$F$22,MATCH($A3,Positions!$A$2:$A$22,0)))

which works fine, however I want to now obtain a sum of the results of this function over a range of values. I tried

=SUM(IF(ISBLANK(E3:E32),0,INDEX(Positions!$F$2:$F$22,MATCH($A3:$A32,Positions!$A$2:$A$22,0)))

but changing the single cell values is ISBLANK and MATCH returns me an invalid value. How can I obtain the sum of the first function over a range of cells?

1
excelexcel-formula
it is always helpful to have a screenshot of your data structure. – Marcel
Without going down the VBA route - you'll more than likely need an array formula (commonly known as CSE formulas) although bear in mind that these can be rather intensive on the processor when it comes to calculating the worksheet if you have a lot of data. – SierraOscar
remember that when you change it to an array formula you need to enter the formula with control-shift-enter – Jerry Jeremiah

1 Answers

0
votes

Your current formula doesn't work because INDEX function won't return a range in that setup, try this formula instead, using SUMIF as a "pseudo lookup"

=SUMPRODUCT((E3:E32<>"")+0,SUMIF(Positions!$A$2:$A$22,$A3:$A32,Positions!$F$2:$F$22))

That assumes that you don't have repeated values in Positions!$A$2:$A$22