SOLVED

formula in Countifs

New Contributor

I have a column of dollar values. I am trying to write a formula where I look at three different ranges of dollars in the column and equate each dollar range with a value. For example: cells in the column that are >=$2000 and <=$3000, I want each cell counted as 0.5. Other cells in the same column that are >=$4000 and <=$5000, I want each cell counted as 1.0. And other cells in the column that are >= $6000 and <=$7000, I want them each counted as 2.0. thank you.

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@steve_berne 

=COUNTIFS(B2:B20,">="&2000,B2:B20,"<="&3000)*0.5+COUNTIFS(B2:B20,">="&4000,B2:B20,"<="&5000)*1+COUNTIFS(B2:B20,">="&6000,B2:B20,"<="&7000)*2

You can try this formula.

countifs.JPG 

Excellent!!! thank you! that worked.