Countifs function with indirect function - excluding count based on values in range

0
votes

Hello I am trying to get to the right count based on the following formula. Same count for multiple worksheets in one excel workbook, using INDIRECT function so manager can select on an overview page for what worksheet/month to count (worksheet for each month). Count is based on multiple criteria (COUNTIFS) and now I need to exclude 2 values in column E.

To clarify = DO NOT COUNT if COLUMN E = FT-Temp/Fixed Term OR COLUMN E = PT-Temp/Fixed Term

This is the formula that works (not including the exclusions of the values in column E)

=COUNTIFS(INDIRECT("'"&$L$3&"'!$R$1:$R$9000"),$L$9,INDIRECT("'"&$L$3&"'!$H$1:$H$9000"),N4)

Now I want to exclude FT-Temp/Fixed Term and PT-Temp/Fixed Term (values in column E) : the formula below doesn't return the right number.

=COUNTIFS(INDIRECT("'"&$L$3&"'!$Z$1:$Z$9000"),$L$8,INDIRECT("'"&$L$3&"'!$H$1:$H$9000"),N4, INDIRECT("'"&$L$3&"'!$E$1:$E$9000"),"<>FT-Temp/Fixed Term",INDIRECT("'"&$L$3&"'!$E$1:$E$9000"),"<>PT-Temp/Fixed Term")

Thank you for your help

1
excelexcel-indirect
Does the formula work if the workbook it refers to is open when you input it? – Jerry
The formula looks OK to me. How is the count wrong? – Rory
Are the formulas supposed to be identical apart from the inclusion of the extra criteria for column E? In your first version, the first criteria range is column R; in your second, column Z. – XOR LX

1 Answers

-1
votes

The range was incorrect, the formula was working fine. Due to a macro not working correctly the table range inserted wasn't including all the values in the spreadsheet.