Can you help me understand this formula?

BenRichards

New Member
=IF(COUNT.IF(\$GR\$3:\$GR\$18;"<="&GR3)=0;" ";(COUNT.IF(\$GR\$3:\$GR\$18;"<="&GR3)))

Hi everybody. I'm having trouble understanding the above formula. If there are different letters in the range \$GR\$3:\$GR\$18, this formula will give a number of how the letter in question (in cell GR3) relates to the others, what the letter's number would be if we were to sort the letters from smallest to largest.

However, if I enter numbers in that range, the formula returns nothing. I'd like for it to work with both letters and numbers. Any ideas?

Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
not an anser to your question but change in the formula.

COUNT.IF to countif (without a dot).

not an anser to your question but change in the formula.

COUNT.IF to countif (without a dot).

That would cause an error.

Ben, could you give some examples of cell content and expected results?

As far as I can see, the only time it should return nothing would be if GR3 was empty.

Last edited:
Dutch (AANTAL.ALS) English (COUNTIF)

@Jasonb75

Please explain why that cause an error.

I don't understand, i realy appreciate it if you could explain it to me.

There are differences between various English versions of excel, one uses COUNT.IF instead of COUNTIF, using the wrong one would return a #NAME? error, the same as if you tried using English functions in your Dutch settings, etc.

@jasonb75

Code:
There are differences between various English versions of excel

Thanks for the reply, and point me on this one.

Realy appreciated.

I've been trying to find exactly which version with no success, maybe the OP can answer that for us.

I note that COUNT.IF(\$GR\$3:\$GR\$18;"<="&GR3)=0 will always be FALSE , unless this formula is changed by dragging.

The OP could get the same result by using the formula =COUNT.IF(\$GR\$3:\$GR\$18;"<="&GR3)
and formatting the cell with the custom format General;General;""

Oeldere, I'm using Excel 2013, the language is not English, hence the COUNT.IF, which I translated so people would know what sort of a function I'm talking about. I didn't alter anything else in the formula.

But my point was that I wanted it to work with numbers as well as letters.

jasonb75, the formula is in cells GQ3-GQ18 (dragged down from the top one).

If I put:

A in GR3
B in GR4
C in GR5

Then the results of the formula will be:

14 in GQ3
15 in GQ4
16 in GQ5

However, if I enter numbers in GR-cells, there are no results in the GQ-cells. I'd like to sort numbers in the same fashion as well.

I suspect GR3:GR18 are formatted as Text. Are the numbers aligned to the left of the cells?

Replies
3
Views
562
Replies
9
Views
318
Replies
2
Views
330
Replies
1
Views
373
Replies
3
Views
241

1,203,758
Messages
6,057,180
Members
444,913
Latest member
ILGSE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back