why can I used counta formula on a closed workbook (xlsm) but not a countif formula??

[Spyros13]

That's my question.

I was able to get accurate results of counta(A:A) on a closed xlsm workbook, but countif(A:A,"*") on same workbook gave value unless I opened the workbook.

(and as to why the macro I posted just a sec ago works ACCURATELY for .xlsm files but not .xsls, xlsx files, Is Another, entirely diffferent question).

BUt im more converned with why referencing a closed .xlsm bookbook with

counta

works first off, but doesn't by

countif

is there something inherent in counta which enables it to work first hand, but theres something with countif that doesnt? why the disparity in performance?? behavior?? (and on closed workbooks?).

Because im thinking if Counta can work on a closed workbook, and countif cant, (or not as much) , then the same level of power/efficiency carries through to their operational behavior and perfomance when workbooks are open.

which brings me to the top question: Is there a more efficient way to reference and count ranges and rows? (on A. closed and then B. open workbooks) , than the "count" family of functions???

 

[jmacleary]

OK. No formulas in excel that I am aware of will work correctly 'out of the box' against closed workbboks. If you open the links then they will update. I am sceptical that your counta actually worked against a closed workbook.

 

[MARK858]

Counta on a closed workbook gives the correct answer for me with =COUNTA('C:\Users\MARK858\Desktop\[testx.xlsm]Sheet1'!$A$1:$A$15) typed in freehand so there was no previous reference to the workbook or sheet and no links opened.

 

[MARK858]

@Spyros13 please don't ask the same question multiple times in different threads (might be worth you looking at the forum rules).

 

[Spyros13]

ok. sorry my bad. i got impatient. my bad. i wont again. try it your self Jimacleary. It worked on an closed .xlsm file but not properly on a closed .xlsx or .xls (no instead, when the pointer was to those it just counted all the rows of the range). But when it was pointing to a closed .xlsm file it gave an accurate result. ok, its not the most straightforward of things, but i just tried it again on a file i hadnt opened since January, which had a filter active, and it returned currect result. Maybe you should close this question as you already answered (Aladins answer)

 

[MARK858]

It worked on an closed .xlsm file but not properly on a closed .xlsx or .xls (no instead, when the pointer was to those it just counted all the rows of the range)

I just tried it with xls, xlsx, xlsm and xlsb files and all returned the correct count for the number of used cells.

 

[Spyros13]

			That's super strange for me !! Other people said the same too. Whats wrong with my excel? machine? i might try again after I restart. Im using Window 10 MSoffice 2016, stand alone. HP Pavilioon. Might be because of my settings, or might just work after a restart. All CountA:A does on a closed xlsx, xls, is count all the rows (i,e. not work properly). It only works fully properly for me on an .xlsm. Ofccourse, if I open them it gives correct result.
			I just tried it with xls, xlsx, xlsm and xlsb files and all returned the correct count for the number of used cells.

 

[RoryA]

None of the xxxIF functions work with closed workbooks.

 

[Spyros13]

OK. But do you know why my countA works properly on a closed .xlsm file, but doesnt work properly on a closed .xls or .xlsx file? (where for me, it returns the count of all the rows and not the count of the used cells?) . Maybe different new question I need to make "Why does my countA work accirately on a closed xlsm but not a closed .xls/.xlsx?) .

Maybe it does work properly, but the attributes of a counta on closed .xls/.xlsx are different to the attributes of a counta (its approach/behavior & execution) on a open .xls/xlsx, and again are again dfferent on a closed .xlsm . Somhing about .xlsm files allow couna to work as youd imagine, and something 'lacking' in .xls/xlsx files that make it work in a different way? Gosh trust me to get so pinikity over this, But i think it is important.*

Is that a Different question or no? I don't want to get banned by posting something that can be seen as same question (although in my mind, thats a related by different quetion.)

*Then again the whole question could just relate to my computer/excel, as others say they havn't noticed a difference.

 

[RoryA]

You won't get banned for that.

That is not something I've seen before, or heard of. I suspect it has to do with the workbooks in question, rather than the function itself.