Calculate Formulas with Visible Rows Only

[resourceguru]

Hello,

I'm designing a resourcing spreadsheet and am trying to customize the formulas so it only calculates numbers for the rows that are filtered out/visible. From somewhat similar posts, it seems like I should do something with the sumifs and function 9. I got stuck after dealing with the offset components.

For context:

• Ultimately, I want the resourcing graph to update based on which resource I've selected.
• To do that, I want to update the formulas so that they are dependent on which resource I've selected/filtered out--i.e., only the rows that are visible

Current state:

• Right now, I have formulas that only makes calculations based on certain criteria.
• E.g., SUMIFS(H:H,$B:$B,"Sold",$E:$E,"FTE")/5 is used to sum all the resource days on col H if the person is FTE and doing sold work, then divide by 5 to convert that number into headcounts for that week.

Next Steps:

• I want to customize that formula so that it only makes the calculations for resources I've selected/filtered out.
• How should I proceed with this challenge? In case it helps, I've uploaded a sample spreadsheet to get that basic formula down before applying it to the other calculations
• Spreadsheet link:

https://drive.google.com/open?id=1SijEScMNZmOOQ7cbsyJ9Uh8CnJAKQwfa

Thanks in advance!

 

[resourceguru]

To clarify: the resources in question that I want to filter is Col C ("Name"). So what I'm trying to do will be: calculate the headcoutns doing sold work for each week--for only Bob!

 

[Nishant94]

Try this with Ctrl+Shift+Enter in H7:

=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5

Copy the formula down and across.

 

[Nishant94]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								18-Jan	25-Jan	1-Feb	8-Feb
2	Projects	Project Status	Name	Role	Employee Status
3	Project A	Sold	Bob	Engineer	FTE			3	2	3	5
4	Project B	Sold	Ron	Designer	FTE			5	3	2	3
5	Project C	Planned	Seith	PM	FTE			1	2	3	5
6
7
8	Headcounts Doing Sold Work for Each Week							1.6	1	1	1.6
9
Sheet1

Cell Formulas
Range	Formula
H8	{=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[resourceguru]

	A	B	C	D	E	F	G	H	I	J	K
1								18-Jan	25-Jan	1-Feb	8-Feb
2	Projects	Project Status	Name	Role	Employee Status
3	Project A	Sold	Bob	Engineer	FTE			3	2	3	5
4	Project B	Sold	Ron	Designer	FTE			5	3	2	3
5	Project C	Planned	Seith	PM	FTE			1	2	3	5
6
7
8	Headcounts Doing Sold Work for Each Week							1.6	1	1	1.6
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula H8 {=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5} <thead> </thead><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>

Thanks! I it works with the sample data. How do I apply it so that it accounts for the Employee Status condition as well? E.g., not everyone is FTE, some will be contractor. This is why the current formula contains $E:$E,"FTE", which makes sure that I only add the data if the person is doing sold work AND is an FTE.

 

[Nishant94]

​

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								18-Jan	25-Jan	1-Feb	8-Feb
2	Projects	Project Status	Name	Role	Employee Status
3	Project A	Sold	Bob	Engineer	FTE			3	2	3	5
4	Project B	Sold	Ron	Designer	FTE			5	3	2	3
5	Project C	Planned	Seith	PM	FTE			1	2	3	5
6
7
8	Headcounts Doing Sold Work for Each Week							1.6	1	1	1.6
9
Sheet1

Cell Formulas
Range	Formula
H8	{=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[resourceguru]

Thanks Nishant94!

I tested it on the broader spreadsheet and it works pretty well. I did have to add a "$" to B3 so that value remains static when applying formulas to the subsequent columns.

I.e., from:
SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5
to:
SUMPRODUCT((IF(SUBTOTAL(3,OFFSET($B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5

This will take the resourcing sheet design to the next level.