SUMIF INDEX MATCH ERROR #Value

[mills89]

Hi

I am having trouble with the below formula. The formula returns #value . I'm hoping someone can spot something that I can't see.

=SUMIFS(((INDEX('Cash Flow items'!$C$4:$J$5,MATCH('Outflow Summary'!$B4,'Cash Flow items'!$B$4:$B$5,0),MATCH('Outflow Summary'!$C4,'Cash Flow items'!$C$3:$J$3,0)))),'Cash Flow items'!$A$4:$A$5,'Outflow Summary'!$F$2)

I have run an index formula and sumif formula with success however no success when combining the 2.

 

[Aladin Akyurek]

Hi

I am having trouble with the below formula. The formula returns #value . I'm hoping someone can spot something that I can't see.

=SUMIFS(((INDEX('Cash Flow items'!$C$4:$J$5,MATCH('Outflow Summary'!$B4,'Cash Flow items'!$B$4:$B$5,0),MATCH('Outflow Summary'!$C4,'Cash Flow items'!$C$3:$J$3,0)))),'Cash Flow items'!$A$4:$A$5,'Outflow Summary'!$F$2)

I have run an index formula and sumif formula with success however no success when combining the 2.

Control+shift+enter, not just enter:

=SUM(IF('Cash Flow items'!$A$4:$A$5='Outflow Summary'!F2,IF('Cash Flow items'!$B$4:$B$5='Outflow Summary'!B4,IF('Cash Flow items'!$C$3:$J$3='Outflow Summary'!C4,'Cash Flow items'!$C$4:$J$5))))

 

[mills89]

Control+shift+enter, not just enter:

=SUM(IF('Cash Flow items'!$A$4:$A$5='Outflow Summary'!F2,IF('Cash Flow items'!$B$4:$B$5='Outflow Summary'!B4,IF('Cash Flow items'!$C$3:$J$3='Outflow Summary'!C4,'Cash Flow items'!$C$4:$J$5))))

Hi Aladin

Thanks for your quick reply, that worked, thankyou!

Do you mind explaining why the original formula didn't work and why the formula you provided did?

Thanks

 

[Aladin Akyurek]

Hi Aladin

Thanks for your quick reply, that worked, thankyou!

You are welcome.

Do you mind explaining why the original formula didn't work and why the formula you provided did?

Thanks

SUMIFS require range specifications of the same dimension. For example: SUMIFS(A2:A4,B2:B4,"x") is admissible; SUMIFS(A2:C4,B2:B4,"x") is not.

The array formula constructs the range to sum using IF-filters. that is, SUM sums whatever range (array) the chain of IF-filters deliver.