Looking for a way to count a number set and update another cell with whatever that count is?

[gnarwhal19]

So first off, I'm extremely new to Excel so I apologize if I'm not using the correct terminology.

My query is this: I have a spreadsheet where I have to count how many times a certain number comes up and if so I have to tally how many times it shows.
For instance, I'll have a column with a cell that has a number in it for example "120" and I need to know how many sets of 50 show up in that cell. So this would have 2 (50s) in it as well as 1 (20) totaling 120. I'm wondering if there's a formula that can calculate how many times a group of "50" shows up in a given cell AND the remaining difference if there are any. Another problem I am working on is if a number is greater (>) than 6 or less than (<) 6 and how many times that shows up.

I have included a sample table so you can visualize the types of numbers I am working with, and if you need any clarification on what I'm asking for please just let me know.

Any help even if it's just sending me to another forum or watching a youtube video I don't mind, it's all appreciated!

Thank you.

forum test columns.xlsx
	A	B
1	25	25
2
3
4
5	11,000
6
7
8
9
10	125
11	180
12	250
13	50
14	11,605	0
15
16
17		100
18
19
20
21
22
23	0	100
24
25
26	150
27	150
28	300	0
29
30
31		150
32		35
33		50
34		2
35		3
36		3
37		5
38		2
39		2
40		2
41		3
42		2
43		3
44		1
45		2
46		50
47		10
48		25
49	0	350
50
51	11,930	475
Sheet1

Cell Formulas
Range	Formula
A14:B14	A14	=SUM(A4:A13)
A23:B23	A23	=SUM(A17:A22)
A28:B28	A28	=SUM(A26:A27)
A49:B49	A49	=SUM(A31:A48)
A51:B51	A51	=SUM(A14+A23+A28+A49+A1)

 

[Bosquedeguate]

from your example there are a few questions ...
- what column(s) should be searched for 50 ... A?
- column A contains numbers less than 50 any many greater than 50; should the values that are greater than 50 be divided by 50 to determine the "number of 50s" in the value.
- there are totals in both columns - is the part of what should be included in the count.
- the only values less than 6 are in column B (is this the column that should be counted for <6 and >6?

your description is a bit difficult to fully understand what you need. Please clarify.

 

[Peter_SSs]

Welcome to the MrExcel board!

I agree that your questions are a little unclear, but see if these idea are headed in the right direction. If not, it might help indicate what further information (& examples of results) you need to give us.

23 08 23.xlsm
	A	B	C
1		50s	Remainder
2	120	2	20
3	25	0	25
4	11000	220	0
5	286	5	36
6	0	0	0
7	350	7	0
8
9
10
11		How many >=6	How many <6
12	8	5	3
13	5
14	2
15	6
16	9
17	22
18	0
19	15
20
Counts

Cell Formulas
Range	Formula
B2:B7	B2	=INT(A2/50)
C2:C7	C2	=MOD(A2,50)
B12	B12	=COUNTIF(A12:A19,">=6")
C12	C12	=COUNTIF(A12:A19,"<6")

 

[gnarwhal19]

from your example there are a few questions ...
- what column(s) should be searched for 50 ... A?
- column A contains numbers less than 50 any many greater than 50; should the values that are greater than 50 be divided by 50 to determine the "number of 50s" in the value.
- there are totals in both columns - is the part of what should be included in the count.
- the only values less than 6 are in column B (is this the column that should be counted for <6 and >6?

your description is a bit difficult to fully understand what you need. Please clarify.

Thank you for trying to help but peter below is actually right on track with what I need!

 

[gnarwhal19]

Welcome to the MrExcel board!

I agree that your questions are a little unclear, but see if these idea are headed in the right direction. If not, it might help indicate what further information (& examples of results) you need to give us.

23 08 23.xlsm
	A	B	C
1		50s	Remainder
2	120	2	20
3	25	0	25
4	11000	220	0
5	286	5	36
6	0	0	0
7	350	7	0
8
9
10
11		How many >=6	How many <6
12	8	5	3
13	5
14	2
15	6
16	9
17	22
18	0
19	15
20
Counts

Cell Formulas
Range	Formula
B2:B7	B2	=INT(A2/50)
C2:C7	C2	=MOD(A2,50)
B12	B12	=COUNTIF(A12:A19,">=6")
C12	C12	=COUNTIF(A12:A19,"<6")

Actually you are right on with this! I'm going to plug it into some of my spreadsheets to test. I'll leave my question up for now but I'll close it if this works for me.

thank you so much!

 

[Peter_SSs]

Actually you are right on with this! I'm going to plug it into some of my spreadsheets to test.

Cheers.
Post back with details if something more is required.

 

[gnarwhal19]

Welcome to the MrExcel board!

I agree that your questions are a little unclear, but see if these idea are headed in the right direction. If not, it might help indicate what further indication (& examples of results) you need to give us.

23 08 23.xlsm
	A	B	C
1		50s	Remainder
2	120	2	20
3	25	0	25
4	11000	220	0
5	286	5	36
6	0	0	0
7	350	7	0
8
9
10
11		How many >=6	How many <6
12	8	5	3
13	5
14	2
15	6
16	9
17	22
18	0
19	15
20
Counts

Cell Formulas
Range	Formula
B2:B7	B2	=INT(A2/50)
C2:C7	C2	=MOD(A2,50)
B12	B12	=COUNTIF(A12:A19,">=6")
C12	C12	=COUNTIF(A12:A19,"<6")

This is so close to what i need I just don't have the knowledge of Excel to tell why it's just a bit off. When I run the INT formula you gave me it works perfectly on a single cell, but if I do it at a range it creates essentially another table for the spillover.

This is fine, but then I have to run a sum total to determine what I need in a given data set. Is this just how Excel works or is there a way to wrap everything in one formula to get the total I'm looking for? I've included a sample table to show you what I mean (I removed the grand totals from the previous example to keep it simple).

Or is there an easy way to remove the zeros so I can look at just the data I need?

The ">6/<6" is perfect and I probably should have been able to figure that out haha.

Thank you for all your help so far!

forum test columns.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2						50					Less50					6		less6
3
4	25	25				0	0	0	0		25	25	0	0		17		12
5						0	0	0	0		0	0	0	0
6						0	0	0	0		0	0	0	0
7						0	0	0	0		0	0	0	0
8	11,000					220	0	0	0		0	0	0	0
9						0	0	0	0		0	0	0	0
10						0	0	0	0		0	0	0	0
11				400		0	0	0	8		0	0	0	0
12						0	0	0	0		0	0	0	0
13	125					2	0	0	0		25	0	0	0
14	180					3	0	0	0		30	0	0	0
15	250					5	0	0	0		0	0	0	0
16	50					1	0	0	0		0	0	0	0
17						0	0	0	0		0	0	0	0
18						0	0	0	0		0	0	0	0
19		100				0	2	0	0		0	0	0	0
20						0	0	0	0		0	0	0	0
21						0	0	0	0		0	0	0	0
22						0	0	0	0		0	0	0	0
23						0	0	0	0		0	0	0	0
24						0	0	0	0		0	0	0	0
25						0	0	0	0		0	0	0	0
26						0	0	0	0		0	0	0	0
27	150					3	0	0	0		0	0	0	0
28	150					3	0	0	0		0	0	0	0
29						0	0	0	0		0	0	0	0
30						0	0	0	0		0	0	0	0
31		150				0	3	0	0		0	0	0	0
32		35				0	0	0	0		0	35	0	0
33		50				0	1	0	0		0	0	0	0
34		2				0	0	0	0		0	2	0	0
35		3				0	0	0	0		0	3	0	0
36		3				0	0	0	0		0	3	0	0
37		5				0	0	0	0		0	5	0	0
38		2				0	0	0	0		0	2	0	0
39		2				0	0	0	0		0	2	0	0
40		2				0	0	0	0		0	2	0	0
41		3				0	0	0	0		0	3	0	0
42		2				0	0	0	0		0	2	0	0
43		3				0	0	0	0		0	3	0	0
44		1				0	0	0	0		0	1	0	0
45		2				0	0	0	0		0	2	0	0
46		50				0	1	0	0		0	0	0	0
47		10				0	0	0	0		0	10	0	0
48		25				0	0	0	0		0	25	0	0
Sheet2

Cell Formulas
Range	Formula
F4:I48	F4	=INT(A4:D48/50)
K4:N48	K4	=MOD(A4:D48,50)
P4	P4	=COUNTIF(A4:D48, ">=6")
R4	R4	=COUNTIF(A4:D48, "<6")
Dynamic array formulas.

 

[Peter_SSs]

to get the total I'm looking for?

I'm not sure what total you are looking for.
Is it just these?

=SUM(INT(A4:D48/50))

=SUM(MOD(A4:D48,50))

If not, can you post the sample data again but remove those formulas and instead manually enter the result(s) that you are looking for and explain again in relation to that data/results.

 

[gnarwhal19]

I'm not sure what total you are looking for.
Is it just these?

=SUM(INT(A4:D48/50))

=SUM(MOD(A4:D48,50))

If not, can you post the sample data again but remove those formulas and instead manually enter the result(s) that you are looking for and explain again in relation to that data/results.

It is what I'm looking for, I just have to combine it with a sum total to get exactly what I need.

Thank you for your help!

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.