How to do Totals where the start and the end of the array can vary?

[Rafaa]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1				Project Budget
2
3				Project Name:
4				Nickname:
5				Reference:
6
7		ID		Project Expenses		Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Total
8																		$ -
9		Header		Artwork														$ -
10		Artwork		Manual - User		$ 100.00												$ 100.00
11		Artwork		Manual - Service				$ 255.00										$ 255.00
12		Artwork		Labels						$ 151.00		$ 255.00						$ 406.00
13		Artwork		Carton												$ 255.00		$ 255.00
14		Header		Total														$ -
15																		$ -
16		Header		Development Samples														$ -
17		Cert_Gas		Specification														$ -
18		Cert_Gas		Lab Testing														$ -
19		Header		Total														$ -
20																		$ -
21																		$ -
22																		$ -
23																		$ -

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

template​

Hi,

I am trying to have an adaptable template for a product development budget spreadsheet where I can insert specific costs for each stage of the process. The idea is to avoid having a big list of all possible costs and then having to delete unused rows, instead we would have a blank template where we select the type of costs we want to include on the table.

The columns "ID" and "Project Expenses" have drop down lists. Each specific ID selected on the "ID" column provides a different range of options to be selected on the adjacent cell under the column "Project Expenses" and If I want to include artwork costs starting on cell D10, for example, I select "Header" ID on cell B9, then I select "Artwork" on cell D9, then select "artwork" ID on cell B10 and then select "Manual user" on cell D10, repeating this process until we get to the stage presented on the sample above my message.

The problem now is how do I do to have the partial total for each type of header? How do I use conditionals to insert the sum function only on the rows where "Total" was selected on the "Project Expenses" column and how do I define the start and the end of the arrays? For instance, the total costs of Artwork in JAN (cell F14) should be the sum from F10 to F13.

Please let me know if it is clear and if not, I can try to explain in a different way. Thanks in advance! Any help would be entirely appreciated.

Cheers,

Rafaa

 

[Glove_Man]

I think you're going to have to use a worker column here.

I would use column C to calculate how many entries are under each header.

In cell C9: =IF(D9="Total",COUNTIF($B$9:B9,B8),"" )

Then copy that down.

In cell F14 you can then have =SUM(OFFSET(F14,-1,0,-$C14,1))

Then copy that across. Of course, you can put the COUNTIF function into the formula directly, but that will become computationally intensive if your sheets get big. So it's up to you.

 

[Rafaa]

Thanks for your reply Glove_Man, but the problem is that after row 8 the table is created differently for each project. What I am trying to find is a way to count the totals between two "Header" IDs.

I don't know how, but when a row is selected to be the Total, I would need to search for the previous "Header", locate its row and then stipulate the start and end of the SUM function. I can't address the array to specific cells as it can be changed. For example, I could have listed the costs for the "Development Samples" before the "Artwork" costs.

You can't see it on the example that I showed, but the costs IDs aren't necessarily all the same for each header. For example, I could have under "Development Samples" Cert_Gas and Cert_Elect as type of costs. So the way you presented to count the entries won't really work in this case. Thank you anyway Glove_Man.

 

[Glove_Man]

Shifting the Goal Posts!!!

But, I think we can come at this another way.

This formula is an array formula, so you need to use Ctrl+Sht+Enter.

In cell C10 =MAX(IF($B$1:B10="Header",ROW(B1:B10),0)) You should be able to copy this down and get the row number of the most recent "Header" other than the row you are currently in.

 

[Rafaa]

Hi Glove,

Thank you for your assistance. Using your first post, I sort of found a way around, I just need to make sure that all the ID are the same under each header, then I can use the COUNTIF the way you suggested.

For the total formula (Cell F14), I did as follows:
=IF(INDIRECT("D"&ROW())="Total",SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT("C"&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1)),"")

So I can place this formula anywhere and I won't have problems.

Now I need to learn how to populate cells with that formula using the condition of the cell on column D being "Total", but for that I believe I will need VBA, won't I?

Thanks a lot!

 

[Glove_Man]

Yes - that does sound like a VBA thing. Best of luck with it