Forum Discussion
trying to simplify a 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?
Formula works if we have 1 in all cells in column B
Have no idea if they need for something else. If not, we could do bit other way. If to keep, don't merge cells - that's always bad idea - use Center across selection starting from C
Please check in attached
Created a mock-up of what your real schedule may look like and applied your working formula in S2. Then, I used this one in T2:
=SUM(MMULT(TRANSPOSE(B3:B39),--(C3:I39=R2&"*")))It arrived at the same result. Now, I can't tell if this will work for you, but check out the attached file. If this is not what you want, please share your file (a link that gives full access to it on Onedrive or similar).
Here is a sample of what i am doing.
Your formula doesn't seem to work with it or I might be doing something wrong.
try: =SUMPRODUCT(($C$3:$I$39=CONCATENATE(R2,"*"))*($B$3:$B$39))
Your long formula results from your (bad) data structure (possibly a crosstab; blank rows).
Restructure your data into a dataset list and you can use simpler formulas and pivot tables.
