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':

	A	B	C	D	E	F	G
	Role	Project 1	Allocation	Project 2	Allocation	Project 3	Allocation
1	Project Manager	STAR	25%	MOON	25%	SKY	50%
2	Business Analyst	STAR	50%	SKY	50%	N/A	0%
3	Business Analyst	STAR	25%	MOON	65%	SKY	10%
4	Admin	STAR	60%	MOON	20%	SKY	20%

<tbody>
</tbody>

SHEET2 - 'Dropdowns':

	A	B
	Roles	Project Name
1	Project Manager	STAR
2	Business Analyst	MOON
3	Admin	SKY

<tbody>
</tbody>

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
1	Role	Project 1	Allocation	Project 2	Allocation	Project 3	Allocation
2	Project Manager	STAR	25%	MOON	25%	SKY	50%
3	Business Analyst	STAR	50%	SKY	50%	N/A	0%
4	Business Analyst	STAR	25%	MOON	65%	SKY	10%
5	Admin	STAR	60%	MOON	20%	SKY	20%

<tbody>
</tbody>

Raw Data​

And the Dropdowns sheet looks like:

	A	B	C	D
1	Roles	Project Name
2	Project Manager	STAR	25%
3	Business Analyst	MOON	65%
4	Admin	SKY	20%
5	Business Analyst	SKY	60%

<tbody>
</tbody>

Dropdowns​

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

<tbody>
</tbody>

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

 

[beardedcraig]

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

<tbody>
</tbody>

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!