COUNTIF List with multiple results

[raphaeloo]

Hello,

I want to calculate in a single cell how many times a value appears in a column.

The result should be something like:

Age 17-18 - 1
Age 20-30 - 5
Age 30-50 - 1
...and so on

Please see attached image! Many thanks!

 

[cyrilbrd]

Hi, such as this?
Given in A1:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

	Nr
	1	16-18
	2	16-18
	3	17-31
	4	16-18
	5	17-30
	6	16-19
	7	17-31
	Total
	16-17	16-17 0 times
	17-30	17-30 1 times
	16-18	16-18 3 times
	17-31	17-31 2 times
	16-19	16-19 1 times

<colgroup><col style="width:65pt" span="3" width="65"> </colgroup><tbody>
</tbody>

with =B11&" "&COUNTIF($C$3:$C$9,B11)&" times" as a formula in C11.
PS: How do you want your chart to be created based on one cell only? What type of chart do you have in mind?

 

[vogel997]

1 16-18 2 16-18 3 17-31 4 16-18 5 17-30 6 17-30 7 16-19 8 16-18 Result in one cell 16-18: 4 Times; 16-19: 1 Times; 17-30: 2; Times; 17-31: 1 Times Formula. ="16-18: "&TEXT(COUNTIF(B2:B9,"16-18"),"0")&" Times"&"; "&"16-19: "&TEXT(COUNTIF(B2:B9,"16-19"),"0")&" Times"&"; "&"17-30: "&TEXT(COUNTIF(B2:B9,"17-30"),"0")&"; "&" Times"&"; "&"17-31: "&TEXT(COUNTIF(B2:B9,"17-31"),"0")&" Times" Is it this what you're looking for? <colgroup><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[liveinhope]

raphaello
col B = entrant number (as per your screen shot)
col C = age group . to be selected from drop-down your screen shot shows ths drop down list in c23-27 . you have overlapping options (16-17, 17-30 then you have 18-18, 17-31 , finally 26-30)

I moved the "drop down selection " to column E and tidied it up a bit to remove overlaps. You can re-jig the age groups to suit youself. and the formula below will still work provided you keep to 4 age groups . If you want more or less age groups you will need to modify the formula

formula in F1 =CONCATENATE(E1," ",COUNTIF(C2:C20,E1)," times, ",E2," ",COUNTIF(C2:C20,E2)," times, ",E3," ",COUNTIF(C2:C20,E3)," times, ",E4," ",COUNTIF(C2:C20,E4))

I wanted to be in new lines for each age group but cant get that bit to work

	number	agroup		16-18		16-18 3 times, 19-25 3 times, 26-30 5 times, 30-99 1
	1	16-18		19-25
	2	26-30		26-30
	3	26-30		30-99
	4	19-25
	5	26-30
	6	19-25
	7	16-18
	8	16-18
	9	26-30
	10	19-25
	11	26-30
	12	30-99

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 454pt; mso-width-source: userset; mso-width-alt: 22125" width=605><TBODY>
</TBODY>

 

[raphaeloo]

Many thanks guys. The solution provided by liveinhope worked perfectly. I just wonder if it's possible to show each on a new separate line in a single cell instead of having them in line.

Regarding the chart that I planned to do, I decided to create it afterwards in a new spreadsheet with the results typed by me. (I was thinking that it might be impossible to let Excel do this from a single cell).

MANY MANY THANKS!

 

[liveinhope]

Glad my solution works for you

actually you dont need to retype the results to do your graph

in my original solution column E already has the age-groups (your validation list) and column F- has the concatenate formula

- insert a column so that the formula is now in "G" . if you've entered the formula as given it will still work

- in the new , blank column F =COUNTIF(C$2:C$20,E1) . copy down . note the addresses c$2:c$20 have absolute row number.

- now create your graph from range e1:f4

re showing on separate line for each group I couldnt get that to work and I wouldnt mind being able to do that myself so will post as a new thread topic "display single cell as multiple lines "

 

[raphaeloo]

Hi, I used your layout and it saves a lot of time but unfortunately it does fill the space in my page. Instead I will copy the entire collumn (both E & F) in a new page and create the charts from that data.