Forum Discussion

steve_berne's avatar
steve_berne
Copper Contributor
Jul 20, 2022
Solved

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.

  • 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.

     

  • 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.

     

Resources