Forum Discussion
steve_berne
Jul 20, 2022Copper Contributor
formula in Countifs
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.
=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.
- OliverScheurichGold Contributor
=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.
- steve_berneCopper ContributorExcellent!!! thank you! that worked.