trying to simplify a formula

[CaRo110]

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? 

[#2]

@CaRo110 

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

[#3]

try: =SUMPRODUCT(($C$3:$I$39=CONCATENATE(R2,"*"))*($B$3:$B$39))

[#4]

@CaRo110 

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.

[#5]

@Riny_van_Eekelen 

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.

[#6]

@CaRo110 

For that your file that's

=SUM(MMULT(TRANSPOSE(B$3:B$5),--(C$3:I$5=K3)))

[#7]

@SergeiBaklan

thank you. does it work around merged cells? I tried what you gave and it worked, then I added some more information and merged cells and it doesn't work now.

see attached file. 

[#8 · Accepted solution]

@CaRo110 

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

[#9]

understood.

Thank you all for all your help. Your help has been greatly appreciated

[#10]

@CaRo110 , you are welcome