Tidy up/reduce a formula to make it easier to maintain

[beardedcraig]

Hiya,

Is there anyway I could tidy up/reduce this formula to still get the same outcome? I'm basically trying to sum up all the cells across multiple columns where the criteria matches the content of two seperate cells in another worksheet.

Example: Based on the two sheets below - I wanted to sum up the total allocation percentage per role per project.
e.g. I'd like to automatically work out how many Business Analysts are allocated to Project SKY and what the total allocation percentage is across Business Analysts. The answer should be 60% in the example below.

SHEET1 - 'Raw Data':

[TABLE="width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Role[/TD]
[TD]Project 1[/TD]
[TD]Allocation[/TD]
[TD]Project 2[/TD]
[TD]Allocation[/TD]
[TD]Project 3[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project Manager[/TD]
[TD]STAR[/TD]
[TD]25%[/TD]
[TD]MOON[/TD]
[TD]25%[/TD]
[TD]SKY[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Business Analyst[/TD]
[TD]STAR[/TD]
[TD]50%[/TD]
[TD]SKY[/TD]
[TD]50%[/TD]
[TD]N/A[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Business Analyst[/TD]
[TD]STAR[/TD]
[TD]25%[/TD]
[TD]MOON[/TD]
[TD]65%[/TD]
[TD]SKY[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Admin[/TD]
[TD]STAR[/TD]
[TD]60%[/TD]
[TD]MOON[/TD]
[TD]20%[/TD]
[TD]SKY[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]


SHEET2 - 'Dropdowns':

[TABLE="width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Roles[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project Manager[/TD]
[TD]STAR[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Business Analyst[/TD]
[TD]MOON[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Admin[/TD]
[TD]SKY[/TD]
[/TR]
</tbody>[/TABLE]



The formula that I created (that DOES work) is: =SUMIFS('Raw Data'!C:C, 'Raw Data'!A:A, Dropdowns!A2, 'Raw Data'!B:B, Dropdowns!B3)+SUMIFS('Raw Data'!E:E, 'Raw Data'!A:A, Dropdowns!A2, 'Raw Data'!D:D, Dropdowns!B3)+SUMIFS('Raw Data'!G:G, 'Raw Data'!A:A, Dropdowns!A2, 'Raw Data'!F:F, Dropdowns!B3)

BUT - I'm looking to increase the number of Project columns from 3 to 8 so the formula will end up being HUGE. Is there a way to do this cleaner and more concisely????

 

[Eric W]

Welcome to the forum.

I had a devil of a time trying to figure out your example. The formulas and ranges did not seem to line up at all. So I took a shot and dummied up a sample like this:

	A	B	C	D	E	F	G
Role	Project 1	Allocation	Project 2	Allocation	Project 3	Allocation
Project Manager	STAR	MOON	SKY	50%
Business Analyst	STAR	SKY	N/A
Business Analyst	STAR	MOON	SKY
Admin	STAR	MOON	SKY

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]25%[/TD]

[TD="align: right"]25%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]50%[/TD]

[TD="align: right"]50%[/TD]

[TD="align: right"]0%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25%[/TD]

[TD="align: right"]65%[/TD]

[TD="align: right"]10%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]60%[/TD]

[TD="align: right"]20%[/TD]

[TD="align: right"]20%[/TD]

</tbody>

Raw Data​

And the Dropdowns sheet looks like:

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Roles[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Project Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Project Manager[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]STAR[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Business Analyst[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MOON[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Admin[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]SKY[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Business Analyst[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]SKY[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"][/TD]

</tbody>

Dropdowns​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=SUM(IFERROR('Raw Data'!$C$2:$G$5+0,0)*('Raw Data'!$A$2:$A$5=A2)*('Raw Data'!$B$2:$F$5=B2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


You should just be able to change the ranges as needed. Let us know if you have questions.

 

[beardedcraig]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]{=SUM(IFERROR('Raw Data'!$C$2:$G$5+0,0)*('Raw Data'!$A$2:$A$5=A2)*('Raw Data'!$B$2:$F$5=B2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


You should just be able to change the ranges as needed. Let us know if you have questions.

BRILLIANT! Thank you! Thank you! Thank you!

 

[Eric W]

Glad to help!