WBS Subtotal Function?

[mycon73]

Hi,

I'm creating a WBS that has 300 subtotal durations. In the example file in Cell K62, I have the following function:

=SUM(SUBTOTAL_01,SUBTOTAL_02,....SUBTOTAL_55)

I tried to enter, SUBTOTAL_56 through SUBTOTAL_300, but I got a "#NAME" result instead of the total of these 300 Subtotals.

Does Excel have a limit of number of subtotals permitted? More importantly, how can I get the Total for all 300 Subotals? Is there a better function than the one I'm using?

Thanks

[benishiryo]

hi mycon73, you can try copying this into the formula bar:
=SUM(IF(IF(ISNUMBER(B64:B3653),B64:B3653,0)-INT(IF(ISNUMBER(B64:B3653),B64:B3653,0))=0,K64:K3653))

and press CTRL + SHIFT + ENTER

seems correct. i dont know about the limitations of subtotal, but there's definitely better ways if you have to key over hundreds of times.

[mycon73]

Hi Benishiroyo,

What is this function & is it just calculating the subtotals? It does look like it's calculating only the subotals, which is fantastic! How is this function doing this?

Thanks again...

[benishiryo]

for explanation purposes, let's say our range is just from B64:B78. a pattern I saw was that you only sum up when the number on Column B is a whole number. So I want my formula to indentify which are whole numbers. So I used the INT formula. INT will give me the whole number without decimals. so INT(B64) will be 1. INT(B71) will be 2. to know whether it's a whole number, i do a subtraction. B64 - INT(B64) is 1 - 1. i get 0 whenever it is a whole number

but in between your rows are texts, so if i use INT(B78), i will get #VALUE! error. so i need to do the above subtraction only if they are numbers. hence i used ISNUMBER.

so:
(IF(ISNUMBER(B64:B78),B64:B78,0)
is saying if that range are numbers, return that range of numbers. otherwise, return "0". This will be the result:
{1;1.1;1.2;1.3;1.4;1.5;0;2;2.1;2.2;2.3;2.4;2.5;0;0}

INT(IF(ISNUMBER(B64:B78),B64:B78,0))
this is the same as above, but putting an INT formula in front. so the result will be:
{1;1;1;1;1;1;0;2;2;2;2;2;2;0;0}

and if i use the 1st results and subtract the 2nd, i will get:
{0;0.1;0.2;0.3;0.4;0.5;0;0;0.1;0.2;0.3;0.4;0.5;0;0}

and if any of the above results is equals to "0", i will add up column K. hope it's not too confusing. there's probably a better way, but that's what i can think of at that moment.
=)

[mycon73]

Hi Benishiroyo,

There may be other ways, but I don't know if they would be better. I do know that your way was much better than my intent to create a bunch of named cells, then trying to sum those up. Most likely, that function would've became corrupted in some ways if I added or removed rows.

I like your method much better & it seems to work well.

Thanks again...