Formula Help (IsError?)

[Gliffix101]

Good morning,

I have the following formula that adds up COUNTIFS from multiple sheets utilizing a column header search (MATCH). The issue is that the Column Header may not appear on certain sheets. When this happen, the formula errors out. I have heard of the use of IsError to add 0 if there is an error (column missing). I've tried adding it into the formula but with no luck.

=COUNTIFS(March_Week,B$1,INDEX(March_Data,0,MATCH($A8,March_Header,0)),"Pass")+COUNTIFS(April_Week,B$1,INDEX(April_Data,0,MATCH($A8,April_Header,0)),"Pass")+COUNTIFS(May_Week,B$1,INDEX(May_Data,0,MATCH($A8,May_Header,0)),"Pass")+COUNTIFS(June_Week,B$1,INDEX(June_Data,0,MATCH($A8,June_Header,0)),"Pass")+COUNTIFS(July_Week,B$1,INDEX(July_Data,0,MATCH($A8,July_Header,0)),"Pass")+COUNTIFS(August_Week,B$1,INDEX(August_Data,0,MATCH($A8,August_Header,0)),"Pass")+COUNTIFS(September_Week,B$1,INDEX(September_Data,0,MATCH($A8,September_Header,0)),"Pass")+COUNTIFS(MTD_Week,B$1, INDEX(MTD_Data,0,MATCH($A8,MTD_Header,0)),"Pass")

Please note, I am using named ranges. I've traced this error by breaking up the formula and found that I only receive errors when the headers do not appear.

Thanks,

Bill

 

[steve the fish]

Unless your excel version is old you can wrap each countifs formula with IFERROR eg.

=IFERROR(COUNTIFS(March_Week,B$1,INDEX(March_Data,0,MATCH($A8,March_Header,0)),"Pass"),0)

 

[Gliffix101]

This worked for what I needed. Was hoping for a simpler solution since this requires an updating of each aspect of the formula, but this gets me where I need to be. Thanks!

Final Solution:

=IFERROR(COUNTIFS(March_Week,B$1,INDEX(March_Data,0,MATCH($A3,March_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(April_Week,B$1,INDEX(April_Data,0,MATCH($A3,April_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(May_Week,B$1,INDEX(May_Data,0,MATCH($A3,May_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(June_Week,B$1,INDEX(June_Data,0,MATCH($A3,June_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(July_Week,B$1,INDEX(July_Data,0,MATCH($A3,July_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(August_Week,B$1,INDEX(August_Data,0,MATCH($A3,August_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(September_Week,B$1,INDEX(September_Data,0,MATCH($A3,September_Header,0)),"Pass"),0)+IFERROR(COUNTIFS(MTD_Week,B$1,INDEX(MTD_Data,0,MATCH($A3,MTD_Header,0)),"Pass"),0)