Writing Formula To Label Data

[robertsgw]

Hello. I am trying write a formula that will label data based on a range of number. I was thinking of using an ifs statement, but can't figure out how to make it work. I want to label any data between 4000000 and 499999 as Blue, number 510000 as Green, 520000 as red and then any other number outside of this range as other. Any tips?

Also can you write a sumifs formula where the criteria is a range not a specific number?

Thanks,

 

[Georgiboy]

What is your version of Excel?

Are you just looking for the words Blue, Green, Red & Other to be added in a column next to the data or do you want the cells to change colour?

 

[robertsgw]

What is your version of Excel?

Are you just looking for the words Blue, Green, Red & Other to be added in a column next to the data or do you want the cells to change colour?

I am looking for the words to be added in a column next to the data.

 

[Georgiboy]

What is your version of Excel: 2016, 365...?

 

[robertsgw]

What is your version of Excel: 2016, 365...?

I am not sure. I believe its a newer version.

 

[Fluff]

If you click on File, Account you will be able to see what you are using.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[robertsgw]

If you click on File, Account you will be able to see what you are using.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for the tip. I am using 365.

 

[Candyman8019]

Assuming your value is in D5 for example, you can try something like this:
=LET(color,LOOKUP(D5,{0,"Other";400000,"blue";500000,"";510000,"Green";51000,"";520000,"Red"}),IF(color="","Other",color))