Matrix sum over Index Equiv
Solved
KennyQ
Posted messages
3
Status
Member
-
KennyQ Posted messages 3 Status Member -
KennyQ Posted messages 3 Status Member -
Hello,
I want to compute a matrix sum on an INDEX MATCH (with error checking using IFERROR).
IF I do an INDEX MATCH on my matrix, it returns the correct data, however, the matrix sum only returns the first value of the matrix.
Matrix formula on each element of the named matrix Liste (formula that works)
=IF(ISERROR(INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)));0;INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)))
Matrix sum formula on each element of the named matrix Liste (formula that does not work)
=SUM(IF(ISERROR(INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)));0;INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0))))
I've tried a lot, but no success...
I can send the file if needed !
Configuration: Windows 7 / Chrome 30.0.1599.101
I want to compute a matrix sum on an INDEX MATCH (with error checking using IFERROR).
IF I do an INDEX MATCH on my matrix, it returns the correct data, however, the matrix sum only returns the first value of the matrix.
Matrix formula on each element of the named matrix Liste (formula that works)
=IF(ISERROR(INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)));0;INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)))
Matrix sum formula on each element of the named matrix Liste (formula that does not work)
=SUM(IF(ISERROR(INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0)));0;INDEX(septembre;MATCH(Liste;datas!$A:$A;0);MATCH(B1;datas!$1:$1;0))))
I've tried a lot, but no success...
I can send the file if needed !
Configuration: Windows 7 / Chrome 30.0.1599.101
5 answers
Good evening everyone,
While awaiting a response from Le Pingou, here is a proposal:
In B25: =SUMPRODUCT((IF(ISERROR(MATCH(OFFSET(INDEX(septembre,1,1),1,0,ROWS(septembre)-1,1),Liste,0));0;1))*(OFFSET(INDEX(septembre,1,1),1,MATCH(B1,INDEX(septembre,1,0),0)-1,ROWS(septembre)-1,1)))
To be confirmed with Ctrl+Shift+Enter
Otherwise, given the construction of your table (the "calcul" tab), a sum from row 2 to row 23 will do the job.
The array formula is only useful if you don't have this intermediate table.
Best regards
While awaiting a response from Le Pingou, here is a proposal:
In B25: =SUMPRODUCT((IF(ISERROR(MATCH(OFFSET(INDEX(septembre,1,1),1,0,ROWS(septembre)-1,1),Liste,0));0;1))*(OFFSET(INDEX(septembre,1,1),1,MATCH(B1,INDEX(septembre,1,0),0)-1,ROWS(septembre)-1,1)))
To be confirmed with Ctrl+Shift+Enter
Otherwise, given the construction of your table (the "calcul" tab), a sum from row 2 to row 23 will do the job.
The array formula is only useful if you don't have this intermediate table.
Best regards
Le Pingou
Posted messages
12273
Registration date
Status
Contributor
Last intervention
1 476
Hello JvDo,
Thank you very much for your proposal which is identical to mine that I posted yesterday in the early evening.
In fact I’m aiming for another position and it’s thanks to you that I’m discovering my mistake. For information I had noted that the SUM function wasn’t suitable and that SOMMEPROD should be used, followed by the same formula as yours, but it would be preferable to simply use the formula in [B25]: =SOMME(B2:B23) and drag it to the right.
I think KennyQ will find it useful.
Regards.
Le Pingou