hi folks, looking for some tidy up help this morning. I am working on a roster which displays the week down the page. Each day has three periods - Morning, Afternoon, and Night. For each period, there is a line displaying the worker's name and beneath this is the worker's shift hours. Work sites are in the columns across the page. I have a sumif formula to calculate each worker's hours for the week. Its a horizontal sumif with 21 parts (1 part for each shift each day). Is there a better way to achieve the same result?
Formula in the table to the right of the roster is:
=SUMIF(D$7:M$7,Q6,D$8:M$8)+SUMIF(D$10:M$10,Q6,D$11:M$11)+SUMIF(D$13:M$13,Q6,D$14:M$14)+SUMIF(D$16:M$16,Q6,D$17:M$17)+SUMIF(D$19:M$19,Q6,D$20:M$20)+SUMIF(D$22:M$22,Q6,D$23:M$23)+SUMIF(D$25:M$25,Q6,D$26:M$26)+SUMIF(D$28:M$28,Q6,D$29:M$29)+SUMIF($D$31:$M$31,Q6,$D$32:$M$32)+SUMIF($D$34:$M$34,Q6,$D$35:$M$35)+SUMIF($D$37:$M$37,Q6,$D$38:$M$38)+SUMIF($D$40:$M$40,Q6,$D$41:$M$41)+SUMIF($D$43:$M$43,Q6,$D$44:$M$44)+SUMIF($D$46:$M$46,Q6,$D$47:$M$47)+SUMIF($D$49:$M$49,Q6,$D$50:$M$50)+SUMIF($D$52:$M$52,Q6,$D$53:$M$53)+SUMIF($D$55:$M$55,Q6,$D$56:$M$56)+SUMIF($D$58:$M$58,Q6,$D$59:$M$59)+SUMIF($D$61:$M$61,Q6,$D$62:$M$62)+SUMIF($D$64:$M$64,Q6,$D$65:$M$65)+SUMIF($D$67:$M$67,Q6,$D$68:$M$68)
Formula in the table to the right of the roster is:
=SUMIF(D$7:M$7,Q6,D$8:M$8)+SUMIF(D$10:M$10,Q6,D$11:M$11)+SUMIF(D$13:M$13,Q6,D$14:M$14)+SUMIF(D$16:M$16,Q6,D$17:M$17)+SUMIF(D$19:M$19,Q6,D$20:M$20)+SUMIF(D$22:M$22,Q6,D$23:M$23)+SUMIF(D$25:M$25,Q6,D$26:M$26)+SUMIF(D$28:M$28,Q6,D$29:M$29)+SUMIF($D$31:$M$31,Q6,$D$32:$M$32)+SUMIF($D$34:$M$34,Q6,$D$35:$M$35)+SUMIF($D$37:$M$37,Q6,$D$38:$M$38)+SUMIF($D$40:$M$40,Q6,$D$41:$M$41)+SUMIF($D$43:$M$43,Q6,$D$44:$M$44)+SUMIF($D$46:$M$46,Q6,$D$47:$M$47)+SUMIF($D$49:$M$49,Q6,$D$50:$M$50)+SUMIF($D$52:$M$52,Q6,$D$53:$M$53)+SUMIF($D$55:$M$55,Q6,$D$56:$M$56)+SUMIF($D$58:$M$58,Q6,$D$59:$M$59)+SUMIF($D$61:$M$61,Q6,$D$62:$M$62)+SUMIF($D$64:$M$64,Q6,$D$65:$M$65)+SUMIF($D$67:$M$67,Q6,$D$68:$M$68)
