total number count

[ttowncorp]

how do i get a accurate count of a number that repeats in a cell that has text in it as well. for example below

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]data[/TD]
[TD]pizza#[/TD]
[TD]apple#[/TD]
[TD]oranges#[/TD]
[TD]grapes#[/TD]
[/TR]
[TR]
[TD]1234 pizza and wine[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3939 apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2345 oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234 pizza and pie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234 pizza and berries[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234 pizza[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3939 apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	data	pizza	apple	oranges	grapes
2	1234 pizza and wine	4	2	1	0
3	3939 apple
4	2345 oranges
5	1234 pizza and pie
6	1234 pizza and berries
7	1234 pizza
8	3939 apples
Report

Cell Formulas
Range	Formula
B2	=COUNTIF($A$2:$A$8,"*"&B1&"*")

 

[ttowncorp]

thanks this is working. what i didn't know is my sheet has some numbers with no text in it, so the wild cards "*" are not counting those. how do i get around that?

 

[Fluff]

If there is no text, how do you know what to count?

 

[ttowncorp]

im scanning a lot of receipts and some words are fadded and some numbers are fadded. so even though i know what the number and words are im trying to add the these up without having to do this manually

 

[Fluff]

You may know what the faded letters & numbers are & what they mean, but Xl doesn't have a clue.
Why not just retype the faded stuff

 

[ttowncorp]

Counting number with text

is there a way to count a specific number in a cell even if it has text in the cell or not? i tried the wild card like example =COUNTIF($A$2:$A$8,"*"&B1&"*") but this way it's only counting the cells with text and not counting up the ones with just the number in the cell.

 

[Joe4]

Re: Counting number with text

Please provide some examples of what your data looks like, and what you are trying to count.

 

[AlanY]

Re: Counting number with text

try

=COUNTIF($A$2:$A$8,"*"&B1&"*")+COUNTIF($A$2:$A$8,B1)

 

[ttowncorp]

Re: Counting number with text

this works thanks, it's just giving me a green tab error but the code seems to be working, it says the formula in this cell differs from the formulas in this area of the spreadsheet. not sure what that means.