Excel: How do I count multiple figures in a colum?

Question

If I have a column that records multiple figures in any given cell i.e b2 might contain 1,3,4 - b3 3,5,6 etc. What formula would I write to count up how many 3's, 4's 5's etc there are? I can get the result using autofilter by asking to filter for 'contains or equals 3' or 'contains or equals4' etc but I need a formula. What should I write?

Thanks. My particular confusion is with being able to countif cells that contain certain numbers. I.e if B2 = 1,2 And B3 = 2,3 I would like to be able to do a countif that can count two twos. The formula - =countif(B2:B3,2) returns the value 0. Any way of writing a =countif contains 2?

Answer 1

suppose the range that you want to tally is in cells
H20 to H29 .....

Put 1 in A1, 2 in A2, 3 in A3, etc ... and
use the formula:

="There are "&COUNTIF(H20:H29,A1)&" "&A1&"'s"
="There are "&COUNTIF(H20:H29,A2)&" "&A2&"'s"
="There are "&COUNTIF(H20:H29,A3)&" "&A3&"'s"
etc

this will display:
There are X 1's
There are Y 2's
There are Z 3's
etc...

Hope that helps!

Answer 2

Let's say in column C you have the values you want to look for: e.g. C2=1, C3=2, C4=3, C5=4, etc.

Then in column D you can count how many times each value appears in column B. For example in D2:
=SUM(IF( NOT(ISERROR( SEARCH(C2, B$2:B$7))),1,0))

Use Ctrl-Shift-Enter (not just Enter) to input the formula.

The formula first searches for the value (1) in the first cell (B2). If it's found in B2, the search is not an error, so it adds 1. Then it does the same with B3, and so on. This looks in B2:B7; replace with the range your data is in.

Answer 3

You want to use the
CountIf( range, criteria )
function. The range is the cell range and the criteria is what you want to look for.

If you use filtering, you can always highlight the cells listed.
At the bottom of the window you will see some text. Right click on this text to find what you can do with the selected rows. One option is Count and another is Sum.