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

Vote count:

0

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

asked 1 min ago

user2461730

1