formula for summary

[angelosison00]

hi there, can anyone help me about the formula i would use in this summary.
i attached the sample data and summary table. i need to count all the value for the specific variables.
please note that may true data is 1000 lines or more.

[FlameRetired]

In the attached I use a helper column with this formula in E4 and filled down.

Please Login or Register  to view this content.

Also note that the Category cells need to all be filled in ... ie no blanks in order for this to work.

Then in I4 and filled down.

Please Login or Register  to view this content.

[FDibbins]

Not sure where you get 4 for Cat1/Sub1 from? There are only 3 for that combo, and that includes Sub1 and just 1

1st, remove the "sub" part from C, then use this...
I4=COUNTIFS($B$4:$B$25,G4,$C$4:$C$25,RIGHT(H4,1))
copied down as needed

[FlameRetired]

Ford if I understand correctly everything is Sub1 until it is Sub2 and therefore all Categories are Category1 Sub1 in that range and it would include dd in the Values column. I fear I've explained that very poorly.

	B	C	D
3			Value
4	Category1	Sub1
5	Category1	1	aa
6	Category1	2
7	Category1	3	bb
8	Category1	4
9	Category1	5	cc
10			dd
11	Category1	6
12	Category1	7
13	Category1	8
14	Category1	Sub2
15	Category1	1	aa
16	Category1	2	bb
17	Category1	3

[angelosison00]

In the attached I use a helper column with this formula in E4 and filled down.

Please Login or Register  to view this content.

Also note that the Category cells need to all be filled in ... ie no blanks in order for this to work.


Then in I4 and filled down.

Please Login or Register  to view this content.

thanks for this, it helps a lot even though i need a helper column in my data. ill just need to adjust may data.

quick question, what if my sub1 change to not consistent names? example name of place, how do i get the correct sub1??

[angelosison00]

Not sure where you get 4 for Cat1/Sub1 from? There are only 3 for that combo, and that includes Sub1 and just 1

1st, remove the "sub" part from C, then use this...
I4=COUNTIFS($B$4:$B$25,G4,$C$4:$C$25,RIGHT(H4,1))
copied down as needed

sorry but i need the sub part to complete the summary. it should be 4 for Cat1/Sub1 because row 10 also add up.
thanks for the help.

Ford if I understand correctly everything is Sub1 until it is Sub2 and therefore all Categories are Category1 Sub1 in that range and it would include dd in the Values column. I fear I've explained that very poorly.

	B	C	D
3			Value
4	Category1	Sub1
5	Category1	1	aa
6	Category1	2
7	Category1	3	bb
8	Category1	4
9	Category1	5	cc
10			dd
11	Category1	6
12	Category1	7
13	Category1	8
14	Category1	Sub2
15	Category1	1	aa
16	Category1	2	bb
17	Category1	3

this is correct. thanks for the help.

[FDibbins]

sorry but i need the sub part to complete the summary. it should be 4 for Cat1/Sub1 because row 10 also add up.

why? There is nothing in row 10 to indicate that it belongs to anything at all??

If ANYTHING, because of how you have the subX positioned in that table, everything down to row 13 should be sub1 (or is it, anyway?)

[angelosison00]

Not sure where you get 4 for Cat1/Sub1 from? There are only 3 for that combo, and that includes Sub1 and just 1

1st, remove the "sub" part from C, then use this...
I4=COUNTIFS($B$4:$B$25,G4,$C$4:$C$25,RIGHT(H4,1))
copied down as needed

why? There is nothing in row 10 to indicate that it belongs to anything at all??

If ANYTHING, because of how you have the subX positioned in that table, everything down to row 13 should be sub1 (or is it, anyway?)

yes it is still sub1, even though is has no label in column B and C.

[FlameRetired]

@ angelosison00

I am not sure I understand this part:

thanks for this, it helps a lot even though i need a helper column in my data. ill just need to adjust may data.

quick question, what if my sub1 change to not consistent names? example name of place, how do i get the correct sub1??

Please upload an additional workbook that demonstrates that.

[angelosison00]

Not sure where you get 4 for Cat1/Sub1 from? There are only 3 for that combo, and that includes Sub1 and just 1

1st, remove the "sub" part from C, then use this...
I4=COUNTIFS($B$4:$B$25,G4,$C$4:$C$25,RIGHT(H4,1))
copied down as needed

@ angelosison00

I am not sure I understand this part:



Please upload an additional workbook that demonstrates that.

please see attached file. please take note that the shaded part of the summary should be the label that is not consistent in spelling.




thanks

[FlameRetired]

This file ... still with helper columns.

In column E to normalize the Category#

Please Login or Register  to view this content.

In column F to set the limits of the names boundaries

Please Login or Register  to view this content.

Then in Total Count

Please Login or Register  to view this content.

[angelosison00]

hi sorry for to much scenarios.

what if i change the value to numbers instead of text. even though i still need the count not the sum.

thanks