INDEX, MATCH, #NA

[Tinkerbellgcg]

I have 13 worksheets, Jan - Dec & YTD YTD is summing the months, but not all
months have the same number of "OT" in column A, if there is no match in one
month I receive a #n/a error. Can I right an IF(ISNA) into the formula.
Sample below, January sheet contains Batch & Loader, but March sheet does
not contain Loader, so the Loader YDT is showing up as #N/A. Here is the
formula.

=INDEX(January!$A$7:$D$28,MATCH($A$8:$A$28,January!$A$7:$A$28,),MATCH($B$7:$E$7,January!$A$7:$D$7,))+INDEX(February!$A$7:$D$28,MATCH($A$8:$A$28,February!$A$7:$A$28,),MATCH($B$7:$E$7,February!$A$7:$D$7,))+INDEX(March!$A$7:$D$28,MATCH($A$8:$A$28,March!$A$7:$A$28,),MATCH($B$7:$E$7,March!$A$7:$D$7,))+INDEX(April!$A$7:$D$28,MATCH($A$8:$A$28,April!$A$7:$A$28,),MATCH($B$7:$E$7,April!$A$7:$D$7,))+INDEX(May!$A$7:$D$28,MATCH($A$8:$A$28,May!$A$7:$A$28,),MATCH($B$7:$E$7,May!$A$7:$D$7,))+INDEX(June!$A$7:$D$28,MATCH($A$8:$A$28,June!$A$7:$A$28,),MATCH($B$7:$E$7,June!$A$7:$D$7,))+INDEX(July!$A$7:$D$28,MATCH($A$8:$A$28,July!$A$7:$A$28,),MATCH($B$7:$E$7,July!$A$7:$D$7,))

JanuraySheet
OT Bradford Georgetown Orangeville
Batch 23.00 25.00
Loader 10.00 5.00

March
OT Bradford Georgetown Orangeville
Batch 20.00

YTDSheet
OT Bradford Georgetown Orangeville
Batch 43.00 25.00
Loader #n/a #n/a #n/a


Thank you for help

 

[Paul Dunmore]

Any time you have to write a formula with 663 characters in it, there is
probably a better way. As you suggest, you could make your formula even
longer by inserting IF(ISNA) tests to trap the cases where MATCH returns
#N/A, but the better way here is the SUMIF function.

Assuming that your headers in each sheet are in row 1 and column A, put the
following in B2 in the YTD sheet:
=SUMIF(January!$A:$A,$A2,January!B:B)+SUMIF(Febuary!$A:$A,$A2,February!B:B)+
etc for the other months.
Note carefully which columns have absolute $ references and which do not.
You can then copy this cell to the whole range in YTD.