Won't accept my function

[jpbeenen]

Hi,

In 2013 I'm trying to use this formula =COUNTIFS($K:$K,">=M1","<M2")
But it does not accept my function; keeps saying there's a problem and asking if I'm trying to use a function or just write text.

I think there's nothing wrong with the formula syntax and the cell is formatted as a number.

Please why is it not working?

With kind regards,

Jaspert

 

[FormR]

Hi, it doesn't look like your formula posted correctly, if you have less than signs then put a space before and after them and try posting it again.

Having said that though, does your system use ";" as a list separator? if so try try replacing any "," with ";" in the formula.

 

[jpbeenen]

Hi,

Tnx for your quick help!

I havre tried both, but no luck (probably doesn't have anything to do with luck hehe)

=COUNTIFS($K:$K,">=M1","<M2") <-- Doesn't work (my starting position)
=COUNTIFS($K:$K;">=M1";"<M2") <-- Says: too few arguments in this formula

=COUNTIFS($K:$K," > =M1"," < M2") <-- Doesn't work (my starting position, with spaces)
=COUNTIFS($K:$K;" > =M1";" < M2") <-- Doesn't work (my starting position, with spaces + , replaced for

 

[Jonmo1]

Try

=COUNTIFS($K:$K," > ="&M1,$K:$K," < "&M2)

 

[jpbeenen]

Same result

I could try emailing the excell?

 

[Jonmo1]

Same result

Did you see FormR's post about using ; instead of , ?

Having said that though, does your system use ";" as a list separator? if so try try replacing any "," with ";" in the formula.

 

[jpbeenen]

I did, see Today, 03:05 PM

Ah good point! I now tried:

=COUNTIFS($K:$K;" > ="&M1;$K:$K;" < "&M2)

Replacing all the , .. it now accepts the function! I also see the rightcoloured borders around the ranges and cells. It's just not yet giving me the right answer
I'll try and figure it out since I just got a ot further!

Tnx guys. If I sill can't get it working I'll get back here

MvG

Jaspert

 

[Jonmo1]

Ok, good progress.

It's just not yet giving me the right answer

What answer DOES it give you?
Is it returning 0, or a NON 0 number that is wrong?

Can you post some sample data, and what results you're expecting?

 

[jpbeenen]

What I'm looking for is that the formula counts the nr of time a value in K:K is between N1 and N2 ... being =>N1 and <N2

But now the value is 0?

 

[Jonmo1]

You're picture didn't come through.
Try using an HTML tool (see my signature for a link to one)
This will post a nicely formatted table.

Are the values in K (and N1 N2) time values ONLY, or do they include a Date Value as well?

What do these return
=N1>1
=N2>1
=K2>1 - Filled down to end of data in K.