Excel countif to use variable as workbook name

[ilovepurple]

Hi all

I'm a newcomer to this forum so please forgive any non-compliance to any conventions etc.

My questions refers to the following (sample) data

Column A Column B Column C
Workbook Name Criteria Countif(how it should look for correct results)
'abc.xls'!$C:$C 87up =COUNTIF('abc.xls'!$C:$C, 87up)
'abc.xls'!$C:$C 88up =COUNTIF('abc.xls'!$C:$C, 88up)
'def.xls'!$C:$C 87up =COUNTIF('def.xls'!$C:$C, 87up)
'def.xls'!$C:$C 88up =COUNTIF('def.xls'!$C:$C, 88up)

 

[ilovepurple]

Sorry, the data was badly formatted. I'll try to give it better here:

Column A             Column B             Column C
Workbook Name    Criteria    Countif(how it should look for correct results)
'abc.xls'!$C:$C       87up      =COUNTIF('abc.xls'!$C:$C, 87up)
'abc.xls'!$C:$C       88up      =COUNTIF('abc.xls'!$C:$C, 88up)
'def.xls'!$C:$C       87up      =COUNTIF('def.xls'!$C:$C, 87up)
'def.xls'!$C:$C       88up      =COUNTIF('def.xls'!$C:$C, 88up)

 

[T. Valko]

Hi all

I'm a newcomer to this forum so please forgive any non-compliance to any conventions etc.

My questions refers to the following (sample) data

Column A Column B Column C
Workbook Name Criteria Countif(how it should look for correct results)
'abc.xls'!$C:$C 87up =COUNTIF('abc.xls'!$C:$C, 87up)
'abc.xls'!$C:$C 88up =COUNTIF('abc.xls'!$C:$C, 88up)
'def.xls'!$C:$C 87up =COUNTIF('def.xls'!$C:$C, 87up)
'def.xls'!$C:$C 88up =COUNTIF('def.xls'!$C:$C, 88up)

If you're wanting to "build" a formula by referring to cells that contain strings for the arguments then the source file(s) MUST be open for the formula to work properly. This is usually not desirable.

 

[ilovepurple]

Hi T. Valko

Thanks for your reply.

My source files are open but =COUNTIF(A1, B1) doesn't work.

I even tried =COUNTIF(CELL("contents",A1), B1) but that didn't help either

 

[T. Valko]

Hi T. Valko

Thanks for your reply.

My source files are open but =COUNTIF(A1, B1) doesn't work.

I even tried =COUNTIF(CELL("contents",A1), B1) but that didn't help either

'abc.xls'!$C:$C

I assume abc.xls is the file name but what is the sheet name?

The syntax to refer to a file that is already open is:

[abc.xls]Sheet1!A1

Or, if the file name contains spaces or numbers:

'[abc 1.xls]Sheet1'!A1

So, if you want to put the components of the path in cells I would do it like this:

A2 = abc.xls
B2 = sheet1
C2 = C:C
D2 = criteria = 87up

Then the formula would be:

=COUNTIF(INDIRECT("'["&A2&"]"&B2&"'!"&C2),D2)

 

[ilovepurple]

That worked perfectly! Thanks a ton for all the help

 

[T. Valko]

That worked perfectly! Thanks a ton for all the help

You're welcome. Thanks for the feedback!