Hello All,

I have this formula that I worked on.

=COUNTIF($C$3:$I$3,CONCATENATE(R2,”*”))*$B$3+COUNTIF($C$5:$I$5,CONCATENATE(R2,”*”))*$B$5+COUNTIF($C$7:$I$7,CONCATENATE(R2,”*”))*$B$7+COUNTIF($C$9:$I$9,CONCATENATE(R2,”*”))*$B$9+COUNTIF($C$10:$I$10,CONCATENATE(R2,”*”))*$B$10+COUNTIF($C$11:$I$11,CONCATENATE(R2,”*”))*$B$11+COUNTIF($C$13:$I$13,CONCATENATE(R2,”*”))*$B$13+COUNTIF($C$14:$I$14,CONCATENATE(R2,”*”))*$B$14+COUNTIF($C$16:$I$16,CONCATENATE(R2,”*”))*$B$16+COUNTIF($C$17:$I$17,CONCATENATE(R2,”*”))*$B$17+COUNTIF($C$18:$I$18,CONCATENATE(R2,”*”))*$B$18+COUNTIF($C$19:$I$19,CONCATENATE(R2,”*”))*$B$19+COUNTIF($C$21:$I$23,CONCATENATE(R2,”*”))*$B$21+COUNTIF($C$25:$I$25,CONCATENATE(R2,”*”))*$B$25+COUNTIF($C$26:$I$26,CONCATENATE(R2,”*”))*$B$26+COUNTIF($C$27:$I$27,CONCATENATE(R2,”*”))*$B$27+COUNTIF($C$28:$I$28,CONCATENATE(R2,”*”))*$B$28+COUNTIF($C$30:$I$30,CONCATENATE(R2,”*”))*$B$30+COUNTIF($C$32:$I$32,CONCATENATE(R2,”*”))*$B$32+COUNTIF($C$34:$I$34,CONCATENATE(R2,”*”))*$B$34+COUNTIF($C$35:$I$35,CONCATENATE(R2,”*”))*$B$35+COUNTIF($C$36:$I$36,CONCATENATE(R2,”*”))*$B$36+COUNTIF($C$37:$I$37,CONCATENATE(R2,”*”))*$B$37+COUNTIF($C$39:$I$39,CONCATENATE(R2,”*”))*$B$39

The formula basically takes the value in the R column and checks if it is in any field between the c and i columns. if there is, it counts the amount of appearances and multiplies the count by the number in the b column but as you can see, i have to do them row by row.

Is there any way to simplify the formula? 

​Hello All, I have this formula that I worked on. =COUNTIF($C$3:$I$3,CONCATENATE(R2,”*”))*$B$3+COUNTIF($C$5:$I$5,CONCATENATE(R2,”*”))*$B$5+COUNTIF($C$7:$I$7,CONCATENATE(R2,”*”))*$B$7+COUNTIF($C$9:$I$9,CONCATENATE(R2,”*”))*$B$9+COUNTIF($C$10:$I$10,CONCATENATE(R2,”*”))*$B$10+COUNTIF($C$11:$I$11,CONCATENATE(R2,”*”))*$B$11+COUNTIF($C$13:$I$13,CONCATENATE(R2,”*”))*$B$13+COUNTIF($C$14:$I$14,CONCATENATE(R2,”*”))*$B$14+COUNTIF($C$16:$I$16,CONCATENATE(R2,”*”))*$B$16+COUNTIF($C$17:$I$17,CONCATENATE(R2,”*”))*$B$17+COUNTIF($C$18:$I$18,CONCATENATE(R2,”*”))*$B$18+COUNTIF($C$19:$I$19,CONCATENATE(R2,”*”))*$B$19+COUNTIF($C$21:$I$23,CONCATENATE(R2,”*”))*$B$21+COUNTIF($C$25:$I$25,CONCATENATE(R2,”*”))*$B$25+COUNTIF($C$26:$I$26,CONCATENATE(R2,”*”))*$B$26+COUNTIF($C$27:$I$27,CONCATENATE(R2,”*”))*$B$27+COUNTIF($C$28:$I$28,CONCATENATE(R2,”*”))*$B$28+COUNTIF($C$30:$I$30,CONCATENATE(R2,”*”))*$B$30+COUNTIF($C$32:$I$32,CONCATENATE(R2,”*”))*$B$32+COUNTIF($C$34:$I$34,CONCATENATE(R2,”*”))*$B$34+COUNTIF($C$35:$I$35,CONCATENATE(R2,”*”))*$B$35+COUNTIF($C$36:$I$36,CONCATENATE(R2,”*”))*$B$36+COUNTIF($C$37:$I$37,CONCATENATE(R2,”*”))*$B$37+COUNTIF($C$39:$I$39,CONCATENATE(R2,”*”))*$B$39 The formula basically takes the value in the R column and checks if it is in any field between the c and i columns. if there is, it counts the amount of appearances and multiplies the count by the number in the b column but as you can see, i have to do them row by row. Is there any way to simplify the formula?   Read More

​