Sep 27 2024 03:24 PM
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?
Sep 27 2024 11:12 PM
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).
Sep 27 2024 11:12 PM
try: =SUMPRODUCT(($C$3:$I$39=CONCATENATE(R2,"*"))*($B$3:$B$39))
Sep 28 2024 02:41 PM
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.
Sep 30 2024 12:59 PM - edited Sep 30 2024 01:00 PM
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.
Sep 30 2024 01:06 PM
Sep 30 2024 01:25 PM
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.
Sep 30 2024 01:37 PM
SolutionFormula 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
Sep 30 2024 03:24 PM
Sep 30 2024 01:37 PM
SolutionFormula 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