CountIF - Help Please 2

[Nicos67]

Hi All,


How do I write a COUNTIF formula to produce the following results:

I have active linked cells that show totals
I want the countif in another location to show that if:

the cells contents are over a certain number to show 1
the cells contents are under a cettain number to show 3
the cells contents are in between one and another set of numbers to show 2

Many thanks in advance

Nico

 

[GlennUK]

Can you explain in detail what you mean by "the cell contents are over a certain number"?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[JPaules]

What is the ultimate goal of assigning a value of 1, 2, or 3 to these cells? Are you trying to count the number of rows where x is greater than y? There may be a way to do what you want in one step.

--
JP

 

[xlStar]

Create new function in VBA Macro; below is an example to meet your requirement...


Public Function CountIfSpec(OneCell As Range, Low As Integer, High As Integer)
If OneCell <= Low Then
CountIfSpec = 3
ElseIf OneCell >= High Then
CountIfSpec = 1
Else
CountIfSpec = 2
End If
End Function

 

[Nicos67]

Hi Mnat tahnsk for all your replies:

Glenn uk : I ahve to work on RAG scoring (1, = Red Amber = 2 Green = 3)

Each RAG on Parameters
so Red is over a certain mumber e.g if over 10 or over = RED

If between 3 - 9 = Amber

If inder 3 = Red

Thing is I have about 50 mertisc to rage each with their own RAG score

Each Rg score then feed a radar cahrt taht garphically illustatres RGS score

Hope thsi anwers your questions

Nico

 

[JPaules]

Then you aren't really *counting* anything? You just need to indicate the status. Use an IF statment.

This is a formula I had to use to determine the status of an item based on other numbers. Cell G5 told me how many parts I had on hand. J5 and K5 told me how many I needed to do my testing based on 2 different rates.

=IF(G5<J5,0,IF(G5>K5,1,IF(AND(G5>=J5,G5<=K5),2)))

After I had that plugged in, I used conditional formatting. 0s were set for red cells with red text, 1s were green with green text, and 2s were yellow with yellow text.

Does this make sense?

--
JP

 

[Nicos67]

Thanks JPaules


Nico

 

[JPaules]

Glad to have helped. (My husband helped me with that formula so I'll make sure I thank him for you. ;-) )

--
JP