Hopefully the attached will show my problem, which in my ignorance I am at a loss to explain and correct.
Please, what is wrong?
COUNTIFS returns #NAME? and function arguments show #VALUE!
-
-
-
Your formula is fine, it just needs to be entered as an array formula.
That means you must confirm it with ctrl+shift+enter not just enter
-
Thanks for your reply.
However, I've tried that several times but sadly it still doesn't work, I get the same error message. -
It works perfectly for me!
after pressing ctrl+shift+enter the formula will look like this
{=COUNTIFS(B4:B24,">=0.5",C4:C24,"C")}
See how it gets enclosed in curly brackets. DO NOT add the curly brackets manually, Excel adds them when confirmed with ctrl+shift+enter.
With the attached file try changing "C" to "L". the curly brackets disappear as soon as you click anywhere in the formula bar, so after making the change you need to hold down both ctrl and shift keys then press enter.
-
Ok I knew an array formula should not be necessary, COUNTIFS is itself an array formula. I just noticed, while uploading the file that uou have it as an .xls file. What version of Excel do you have? If earlier than 2007 (I think, or maybe even 2010) then COUNTIFS is not supported, hence the error.
-
-
OK, thanks. The curly brackets are shown in my formula. I've been using arrays for some time now, so am familiar with their use.
Re- .xls - you may have hit the nail on the head, as my version is certainly before 2010, originally 2003 but may have been updated to 2007, but certainly no later.
Have you any suggestions for a solution with my early version, please? -
For 2003 and earlier, you can use SUMPRODUCT instead:
=SUMPRODUCT((B4:B24>=0.5)*(C4:C24="C"))
-
Thanks, Rory, but I'd tried that and strangely it didn't work either. I've now obtained Office 2007, and all's well now!!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!