Using COUNT(IF( across multiple closed workbooks

[bratwurst]

Hi all-

I've been searching through the forum and couldn't quite pinpoint my exact scenario.

I am aware xxxIF formulas don't work on referencing closed workbooks so I've been trying to utilize alternative methods.

I'm trying to find how many instances of a particular cell are found in a specific column range. For instance, this formula works:

=COUNT(IF('[filename.xlsx]Tab'!$C$5:$C$1000=B11,1))

But when trying to add additional IF statements/workbooks, I'm not getting accurate results:

=COUNT(IF('[filename.xlsx]Tab'!$C$5:$C$1000=B11, IF('[differentworkbook.xlsx]Tab'!$C$5:$C$1000=B11,1)))

The cell B11 is a text phrase (in this example it is Customer Inquiries).

Any idea what I'm doing wrong? I have about 12 closed workbooks to stick into this formula. All are identical. The current output of that 2nd formula is 1. It should be 3.

Thanks,
brat

 

[arthurbr]

Why not create a summary sheet and start from there? Much easier

 

[johnnyL]

@bratwurst Something like the following should work for you:

=SUMPRODUCT(--('C:\Data\[filename.xlsx]Tab'!$C$5:$C$1000=B11)) + SUMPRODUCT(--('C:\Data\[differentworkbook.xlsx]Tab'!$C$5:$C$1000=B11)) + SUMPRODUCT(--('C:\Data\[anotherworkbook.xlsx]Tab'!$C$5:$C$1000=B11))

 

[bratwurst]

Thanks all! Sorry, forgot to return to conclude the thread.

arthurbr, great info on that summary sheet link, thanks.

johnnyL -- this is exactly what I needed. forgot I could include "+" to attach formulas together. that did the trick.

Thanks again all.

 

[johnnyL]

Your Welcome @bratwurst.