COUNTIF against closed workbooks - #VALUE!

[SimonGeoghegan]

Hi All,

I currently have the below formula looking to calculate the percentage of actions that have been completed on an action plan. Column C counts how many actions there are, and Column N counts how many been completed. There are a number of action plans, so I have this formula multiple times referencing different workbooks.

=COUNTIF('C:\Users\sgeoghegan\Desktop\Clinical Audits\[Book2.xlsm]Clinical Action Plan'!$N:$N,"NOT OK")/COUNTA('C:\Users\sgeoghegan\Desktop\Clinical Audits\[Book2.xlsm]Clinical Action Plan'!$C:$C)

All these workbooks are closed and there #VALUE! is displayed. I have seen threads relating to using array based formulas but I could only find alternatives using SUMPRODUCT instead of a SUMIF. In my case, i'm using COUNTIF and wondered if there was an alternative approach?

Thanks in advance.

Regards,
Simon

 

[shaowu459]

For your reference:

Book1
	A	B	C	D	E
1	Fruit	Price		SUMPRODUCT	COUNTIF
2	Apple	10		2	2
3	Orange	20
4	Pear	30		1	1
5	Peach	40
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(N(B2:B5>20))
E2	E2	=COUNTIF(B2:B5,">20")
D4	D4	=SUMPRODUCT((LEFT(A2:A5)="P")*(B2:B5>35))
E4	E4	=COUNTIFS(A2:A5,"P*",B2:B5,">35")

 

[RoryA]

SUMPRODUCT works for a count too, but you really don't want to use entire columns with it. You'd use something like:

=SUMPRODUCT(--('C:\Users\sgeoghegan\Desktop\Clinical Audits\[Book2.xlsm]Clinical Action Plan'!$N$1:$N$1000="NOT OK"))/COUNTA('C:\Users\sgeoghegan\Desktop\Clinical Audits\[Book2.xlsm]Clinical Action Plan'!$C$1:$C$1000)

 

[SimonGeoghegan]

Thanks RoryA and shaowu459. RoryA - your suggestion worked perfectly!

Every day is a school day as the saying goes