• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum by groups

L

luisconst

New Member
Hello, my name is Luis and I'm from Brazil.


I'm facing a problem that I could not come up to an idea to solve it.

I create groups based on cell position

1 Group1

1.1 Group 2

1.2 Group 2

1.2.1 Group 3

1.3 Group 2


What I want to do is sum the different groups. The Group 2 sums all the group 3 below it and the group 1 sums every Group 2 bellow it

I have an example that I was working on: https://www.dropbox.com/s/779u07gym4qv1d7/Chandoo.xlsx


Thanks for the help
 
Hi Luis ,


I am not clear on what your exact requirement is ; can you clarify ?


It would be nice if you could enter , in any spare column , what values you wish should appear , with specific reference to the data in your workbook.


I am able to make out that the values in the TOTAL column are different , but is this the column that you wish to calculate using formulae ?


Narayan
 
What I want to do is calculate the Total Column.

I make the budget out of one group and than the Total Column should give me the amount based on groups below it.

The Columns "Level", "1", "2", "3", "4" and Aux are just for Calculations. They are auxiliaries.

What I really want to do is Sums based on groups.

The "Code" 3 should sum all the items with "Code" 3.something and the 3.5 should sum all the 3.5.something

I dont think its hard, but its hard to explain.

I updated the link: https://www.dropbox.com/s/779u07gym4qv1d7/Chandoo.xlsx

If you need any other information, please ask. Its very important for me.


Thanks
 
Hi Luis ,


Sorry , but things are still not clear. I have copied and pasted a few columns from your worksheet :

[pre]
Code: 
1	Grupo 1				 R$ 7.00
1.1		Grupo 2			 R$ 7.00
1.1.1			Grupo 3		 R$ 1.00
1.1.1.1				Grupo 4	 R$ 1.00
1.1.2			Grupo 3		 R$ 2.00
1.1.3			Grupo 3		 R$ 1.00
1.1.4			Grupo 3		 R$ 3.00
2	Grupo 1				 R$ 22.00
2.1		Grupo 2			 R$ 5.00
2.1.1			Grupo 3		 R$ 5.00
2.1.1.1				Grupo 4	 R$ 5.00
2.2		Grupo 2			 R$ 2.00
2.3		Grupo 2			 R$ 5.00
2.4		Grupo 2			 R$ 3.00
2.5		Grupo 2			 R$ 7.00
[/pre]
Can you explain the figures in the TOTAL column above ? How have you derived each of the figures ?


Narayan
 
Lets start with the 1.1.1.1 - this one is done eith the budget column so its 1

The 1.1.2, 1.1.3 and 1.1.4 are the same. The Total column is due to the budget column

1.1.1 is the sum of 1.1.1.1 so its 1 too If I had 1.1.1.2 it would have sum it too

The 1.1 is the sum of 1.1.1, 1.1.2, 1.1.3 and 1.1.4 - 1+2+1+3=7

1 is the sum of 1.1 and if I had any other 1.something it would have sum it too.


2.1.1.1 is due Budget column

2.2, 2.3, 2.4 and 2.5 is due Budget column as well

2 is the sum of 2.1, 2.2, 2.3, 2.4 and 2.5 - 5+2+5+3+7=22
 
Hi Luis ,


Can you check out the workbook at the following link :


https://skydrive.live.com/edit.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21181


Narayan
 
Thanks Narayan,


Its working just perfect. Can you explain to me the formulae =Countif(S39:$S$48;S39&"*")

What does the second part mean? S39&"*"


I came up with other solution, but its way more complicated. Its in the file updated.


Thank you
 
Hi Luis ,


Good to know your problem has been resolved.


The COUNTIF function in general , returns a count of the number of occurrences which meet the given criterion ; here we would like to know how many of the items in the range are below the group we are looking at e.g. if we are looking at 1 , we would like to know how many items are there like 1.1 , 1.2 , 1.3 , 1.1.1 , 1.1.2 , 1.1.1.1 ,.... i.e. how many items begin with 1 and then have anything else after that ; the construct S39&"*" means any text that starts with whatever text is there in cell S39 , and then has anything else after that text.


If S39 has 1.1 , then any cell which has text such as 1.1.1 , 1.1.2 , 1.1.1.1 will be counted , but a cell which has 1.2 will not be counted.


The "*" is a wild-card character which can be used with the COUNTIF function ; there are other functions which do not accept wild-cards. Another wild-card character is the question mark ? ; the ? stands for a single character , while the * stands for any number of characters e.g. S?? will match text such as SEE , SEA , SON , but will not match SONG , since this has 4 characters in it ; S?? will match only 3 character text ; S* will match all of these.


Narayan
 
You must log in or register to reply here.
Back
Top