COUNTIFS in External Sheet, Over multiple sheets

[robbo0224]

Hello.
I'm hoping someone will be able to help me with this one, I'm looking to search an external workbook for any occurrences between 2 dates. The dates are obtained by looking up the dates from a business calendar which is in a separate sheet. That is retrieved by the HLOOKP function I've got within the formula.
I've tried but it just doesn't seem to work. I've seen other post about changing to SUMPRODUCT but I don't think that will work correctly when I want a COUNT not a SUM.

Here's my formula;

=IF($B$3<=26,COUNTIFS('K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105,">="&HLOOKUP($B$3,Calendar!$C$8:$AB$19,6,FALSE),'K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105,"<="&HLOOKUP($B$3,Calendar!$C$8:$AB$19,12,FALSE),'K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$D$5:$D$105,"=RGS"),COUNTIFS('K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105,">="&HLOOKUP($B$3,Calendar!$C$22:$AB$33,6,FALSE),'K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105,"<="&HLOOKUP($B$3,Calendar!$C$22:$AB$33,12,FALSE),'K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$D$5:$D$105,"=RGS"))

I'd attached a copy of the sheet but I'm new and don't know how to do that that.

Any help would be fantastic.

 

[FDibbins]

I've seen other post about changing to SUMPRODUCT but I don't think that will work correctly when I want a COUNT not a SUM.

No, that is indeed what you would use

=IF($B$3<=26,sumproduct(--('K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105>=HLOOKUP($B$3,Calendar!$C$8:$AB$19,6,FALSE)),--('K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$B$5:$B$105<=HLOOKUP($B$3,Calendar!$C$8:$AB$19,12,FALSE)),--('K:\Technical & NPD\KPI monitoring\Non-Conformances\[Non-Conformance League Table (2016-17).xlsm]Period 1:Period 12'!$D$5:$D$105="RGS"))

This is obviously untested, but that kinda shows you the syntax/method you need to use

I would also suggest that, instead of searching for the date in each test, you put the hlookups in their own cells and reference them

 

[robbo0224]

Thanks Ford

It works great, but I can only get it to check 1 sheet out of the 12.
They are named Period 1, Period 2 etc all the way to 12.
How do I get it to look through all 12 in one hit. I did have 'Period 1:Period 12' but when I hit enter for some reason it comes out Period 1:[Period 12]Period12 so I think Excel it trying to do something but I don't know what.

Thanks
Rob

 

[FDibbins]

Im not sure that you can do a 3-d count like that, it might be easier to just use that formula for each sheet, and then count those

 

[robbo0224]

Ok, Thanks Ford