Using Index Match to SUM

[oprik]

Hello,
I need to keep track of my colleagues daily/monthly progress and need to sum from multiple tabs (31 tabs) and multiple columns. Every tab is for a day of the month and im try to add Jon Does data from every tab, using Index Match to do this, but if just one of the MATCHs doesn't find a match the equation comes back as "N/A"

1. How can I write it so if one of the MATCHs doesn't match it just keeps on adding up all the other cells?
2. Is their a simpler way of writing this equation?

my current equation looks like this:

=Sum(INDEX('26.1'!C5:C30,MATCH(A5,'26.1'!A5:A32,0)),INDEX('27.1'!C5:C30,match(A5,'27.1'!A5:A32,0)),INDEX('28.1'!C5:C30,MATCH(A5,'28.1'!A5:A32,0)),INDEX('29.1'!C5:C30,MATCH(A5,'29.1'!A5:A32,0)),INDEX('30.1'!C5:C30,MATCH(A5,'30.1'!A5:A32,0)),INDEX('31.1'!C5:C30,MATCH(A5,'31.1'!A5:A32,0)),INDEX('1.2'!C5:C30,MATCH(A5,'1.2'!A5:A32,0)),INDEX('2.2'!C5:C30,MATCH(A5,'2.2'!A5:A32,0)),INDEX('3.2'!C5:C30,MATCH(A5,'3.2'!A5:A32,0)),INDEX('4.2'!C5:C30,MATCH(A5,'4.2'!A5:A32,0)),INDEX('5.2'!C5:C30,MATCH(A5,'5.2'!A5:A32,0)),INDEX('6.2'!C5:C30,MATCH(A5,'6.2'!A5:A32,0)),INDEX('7.2'!C5:C30,MATCH(A5,'7.2'!A5:A32,0)),INDEX('8.2'!C5:C30,MATCH(A5,'8.2'!A5:A32,0)),INDEX('9.2'!C5:C30,MATCH(A5,'9.2'!A5:A32,0)),INDEX('10.2'!C5:C30,MATCH(A5,'10.2'!A5:A32,0)),INDEX('11.2'!C5:C30,MATCH(A5,'11.2'!A5:A32,0)),INDEX('12.2'!C5:C30,MATCH(A5,'12.2'!A5:A32,0)),INDEX('13.2'!C5:C30,MATCH(A5,'13.2'!A5:A32,0)),INDEX('14.2'!C5:C30,MATCH(A5,'14.2'!A5:A32,0)),INDEX('15.2'!C5:C30,MATCH(A5,'15.2'!A5:A32,0)),INDEX('16.2'!C5:C30,MATCH(A5,'16.2'!A5:A32,0)),INDEX('17.2'!C5:C30,MATCH(A5,'18.2'!A5:A32,0)),INDEX('19.2'!C5:C30,MATCH(A5,'19.2'!A5:A32,0)),INDEX('20.2'!C5:C30,MATCH(A5,'20.2'!A5:A32,0)),INDEX('21.2'!C5:C30,MATCH(A5,'21.2'!A5:A32,0)),INDEX('22.2'!C5:C30,MATCH(A5,'22.2'!A5:A32,0)),INDEX('23.2'!C5:C30,MATCH(A5,'23.2'!A5:A32,0)),INDEX('24.2'!C5:C30,MATCH(A5,'24.2'!A5:A32,0)),INDEX('25.2'!C5:C30,MATCH(A5,'25.2'!A5:A32,0)))

 

[Leandroarb]

Hi,


Do you think in using the function ISNA?
Example:

=IF(ISNA([COLOR=#000000][FONT=Inconsolata]Sum[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]INDEX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]'26.1'!C5:C30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MATCH[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]A5[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]'26.1'!A5:A32[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]),0),0),[/FONT][/COLOR][COLOR=#ff0000][FONT=Inconsolata]NEXT INDEX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata][/FONT][/COLOR]

Regards

 

[oprik]

(I shortened my equations just for testing purposes)
First It came back with "too many expressions", I played around with it and put:
=IF(ISNA(Sum(INDEX('26.1'!C5:C30,MATCH(A5,'26.1'!A5:A32,0)))),INDEX('25.2'!$X$5:$X$30,MATCH(V22,'25.2'!$V$5:$V$32,0)))
and came back with "False"
I then put:
IF(ISNA(Sum(INDEX('26.1'!C5:C30,MATCH(A5,'26.1'!A5:A32,0)))),isna(Sum(INDEX('25.2'!$X$5:$X$30,MATCH(V22,'25.2'!$V$5:$V$32,0)))))
and got "False" agian

In this particular example I put "2" in sheet '26.1' and "0" in sheet '25.2' so im expecting "2" to appear in the cell

 

[Leandroarb]

Hi,
It's possíble see your sheet?
If exists secret datas, do a sheet example.



Thansk advance

 

[oprik]

Hi,
It's possíble see your sheet?
If exists secret datas, do a sheet example.



Thansk advance

I tried your advise again and it worked. my problem is/was that my company uses Google sheets so I make all my graphs and charts in Excel, because i know it better, then transfer it to Sheets but not all the equations are the same. Instead of ISNA I used IFERROR("my INDEX/MATCH equation",0) and it worked out great.
The new equation if anyone was curious:
=Sum(iferror(INDEX('26.1'!$C$5:$C$30,MATCH(A5,'26.1'!$A$5:$A$32,0)),0),iferror(INDEX('27.1'!$C$5:$C$30,match(A5,'27.1'!$A$5:$A$32,0)),0),iferror(INDEX('28.1'!$C$5:$C$30,MATCH(A5,'28.1'!$A$5:$A$32,0)),0),iferror(INDEX('29.1'!$C$5:$C$30,MATCH(A5,'29.1'!$A$5:$A$32,0)),0),iferror(INDEX('30.1'!$C$5:$C$30,MATCH(A5,'30.1'!$A$5:$A$32,0)),0),iferror(INDEX('31.1'!$C$5:$C$30,MATCH(A5,'31.1'!$A$5:$A$32,0)),0),iferror(INDEX('1.2'!$C$5:$C$30,MATCH(A5,'1.2'!$A$5:$A$32,0)),0),iferror(INDEX('2.2'!$C$5:$C$30,MATCH(A5,'2.2'!$A$5:$A$32,0)),0),iferror(INDEX('3.2'!$C$5:$C$30,MATCH(A5,'3.2'!$A$5:$A$32,0)),0),iferror(INDEX('4.2'!$C$5:$C$30,MATCH(A5,'4.2'!$A$5:$A$32,0)),0),iferror(INDEX('5.2'!$C$5:$C$30,MATCH(A5,'5.2'!$A$5:$A$32,0)),0),iferror(INDEX('6.2'!$C$5:$C$30,MATCH(A5,'6.2'!$A$5:$A$32,0)),0),iferror(INDEX('7.2'!$C$5:$C$30,MATCH(A5,'7.2'!$A$5:$A$32,0)),0),iferror(INDEX('8.2'!$C$5:$C$30,MATCH(A5,'8.2'!$A$5:$A$32,0)),0),iferror(INDEX('9.2'!$C$5:$C$30,MATCH(A5,'9.2'!$A$5:$A$32,0)),0),iferror(INDEX('10.2'!$C$5:$C$30,MATCH(A5,'10.2'!$A$5:$A$32,0)),0),iferror(INDEX('11.2'!$C$5:$C$30,MATCH(A5,'11.2'!$A$5:$A$32,0)),0),iferror(INDEX('12.2'!$C$5:$C$30,MATCH(A5,'12.2'!$A$5:$A$32,0)),0),iferror(INDEX('13.2'!$C$5:$C$30,MATCH(A5,'13.2'!$A$5:$A$32,0)),0),iferror(INDEX('14.2'!$C$5:$C$30,MATCH(A5,'14.2'!$A$5:$A$32,0)),0),iferror(INDEX('15.2'!$C$5:$C$30,MATCH(A5,'15.2'!$A$5:$A$32,0)),0),iferror(INDEX('16.2'!$C$5:$C$30,MATCH(A5,'16.2'!$A$5:$A$32,0)),0),iferror(INDEX('17.2'!$C$5:$C$30,MATCH(A5,'18.2'!$A$5:$A$32,0)),0),iferror(INDEX('19.2'!$C$5:$C$30,MATCH(A5,'19.2'!$A$5:$A$32,0)),0),iferror(INDEX('20.2'!$C$5:$C$30,MATCH(A5,'20.2'!$A$5:$A$32,0)),0),iferror(INDEX('21.2'!$C$5:$C$30,MATCH(A5,'21.2'!$A$5:$A$32,0)),0),iferror(INDEX('22.2'!$C$5:$C$30,MATCH(A5,'22.2'!$A$5:$A$32,0)),0),iferror(INDEX('23.2'!$C$5:$C$30,MATCH(A5,'23.2'!$A$5:$A$32,0)),0),iferror(INDEX('24.2'!$C$5:$C$30,MATCH(A5,'24.2'!$A$5:$A$32,0)),0),iferror(INDEX('25.2'!$C$5:$C$30,MATCH(A5,'25.2'!$A$5:$A$32,0)),0))

 

[RoryA]

@Leandroarb

Please note that it is against forum rules to suggest emailing files. All questions need to be kept in the open forum. Thanks.

 

[Leandroarb]

@Leandroarb

Please note that it is against forum rules to suggest emailing files. All questions need to be kept in the open forum. Thanks.

Ok Mr. Rory.
Sorry

 

[Leandroarb]

I tried your advise again and it worked. my problem is/was that my company uses Google sheets so I make all my graphs and charts in Excel, because i know it better, then transfer it to Sheets but not all the equations are the same. Instead of ISNA I used IFERROR("my INDEX/MATCH equation",0) and it worked out great.
The new equation if anyone was curious:
=Sum(iferror(INDEX('26.1'!$C$5:$C$30,MATCH(A5,'26.1'!$A$5:$A$32,0)),0),iferror(INDEX('27.1'!$C$5:$C$30,match(A5,'27.1'!$A$5:$A$32,0)),0),iferror(INDEX('28.1'!$C$5:$C$30,MATCH(A5,'28.1'!$A$5:$A$32,0)),0),iferror(INDEX('29.1'!$C$5:$C$30,MATCH(A5,'29.1'!$A$5:$A$32,0)),0),iferror(INDEX('30.1'!$C$5:$C$30,MATCH(A5,'30.1'!$A$5:$A$32,0)),0),iferror(INDEX('31.1'!$C$5:$C$30,MATCH(A5,'31.1'!$A$5:$A$32,0)),0),iferror(INDEX('1.2'!$C$5:$C$30,MATCH(A5,'1.2'!$A$5:$A$32,0)),0),iferror(INDEX('2.2'!$C$5:$C$30,MATCH(A5,'2.2'!$A$5:$A$32,0)),0),iferror(INDEX('3.2'!$C$5:$C$30,MATCH(A5,'3.2'!$A$5:$A$32,0)),0),iferror(INDEX('4.2'!$C$5:$C$30,MATCH(A5,'4.2'!$A$5:$A$32,0)),0),iferror(INDEX('5.2'!$C$5:$C$30,MATCH(A5,'5.2'!$A$5:$A$32,0)),0),iferror(INDEX('6.2'!$C$5:$C$30,MATCH(A5,'6.2'!$A$5:$A$32,0)),0),iferror(INDEX('7.2'!$C$5:$C$30,MATCH(A5,'7.2'!$A$5:$A$32,0)),0),iferror(INDEX('8.2'!$C$5:$C$30,MATCH(A5,'8.2'!$A$5:$A$32,0)),0),iferror(INDEX('9.2'!$C$5:$C$30,MATCH(A5,'9.2'!$A$5:$A$32,0)),0),iferror(INDEX('10.2'!$C$5:$C$30,MATCH(A5,'10.2'!$A$5:$A$32,0)),0),iferror(INDEX('11.2'!$C$5:$C$30,MATCH(A5,'11.2'!$A$5:$A$32,0)),0),iferror(INDEX('12.2'!$C$5:$C$30,MATCH(A5,'12.2'!$A$5:$A$32,0)),0),iferror(INDEX('13.2'!$C$5:$C$30,MATCH(A5,'13.2'!$A$5:$A$32,0)),0),iferror(INDEX('14.2'!$C$5:$C$30,MATCH(A5,'14.2'!$A$5:$A$32,0)),0),iferror(INDEX('15.2'!$C$5:$C$30,MATCH(A5,'15.2'!$A$5:$A$32,0)),0),iferror(INDEX('16.2'!$C$5:$C$30,MATCH(A5,'16.2'!$A$5:$A$32,0)),0),iferror(INDEX('17.2'!$C$5:$C$30,MATCH(A5,'18.2'!$A$5:$A$32,0)),0),iferror(INDEX('19.2'!$C$5:$C$30,MATCH(A5,'19.2'!$A$5:$A$32,0)),0),iferror(INDEX('20.2'!$C$5:$C$30,MATCH(A5,'20.2'!$A$5:$A$32,0)),0),iferror(INDEX('21.2'!$C$5:$C$30,MATCH(A5,'21.2'!$A$5:$A$32,0)),0),iferror(INDEX('22.2'!$C$5:$C$30,MATCH(A5,'22.2'!$A$5:$A$32,0)),0),iferror(INDEX('23.2'!$C$5:$C$30,MATCH(A5,'23.2'!$A$5:$A$32,0)),0),iferror(INDEX('24.2'!$C$5:$C$30,MATCH(A5,'24.2'!$A$5:$A$32,0)),0),iferror(INDEX('25.2'!$C$5:$C$30,MATCH(A5,'25.2'!$A$5:$A$32,0)),0))

Hi Oprik!
Great solution!
The Isna é indicated for the vlookup, when find values, not for when sum values, you are correct.