Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as
shown below, but if i want the number of times .2 occurs, ignoring all other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as
shown below, but if i want the number of times .2 occurs, ignoring all other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as
shown below, but if i want the number of times .2 occurs, ignoring all other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have
been ommited ? Something needs tweeking ?

"Graham" wrote:

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as
shown below, but if i want the number of times .2 occurs, ignoring all other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

I am not sure what you mean by adding together
More details please.

but another question
is this text or numeric.
what do you get with =mid(A1,6,1)
if this is text use
=sumproduct(--(mid(A1:A8,6,1)=2))

also try the equations with just A1 and just A4 instead of A1:A8

"Graham" wrote:

Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have
been ommited ? Something needs tweeking ?

"Graham" wrote:

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as
shown below, but if i want the number of times .2 occurs, ignoring all other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

You might try:

=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",A1:A8))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Graham" wrote in message
...
Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and
have
been ommited ? Something needs tweeking ?

"Graham" wrote:

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a
range
of numbers. The "COUNTIF" function in B1 will work for a whole number
as
shown below, but if i want the number of times .2 occurs, ignoring all
other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

Thank you both for your further input. Both formulae work fine with some test
data, but RagDyeR's works with the real data, so I will run with that! It is
all numeric data. bj's formula is ading together the *.1 & the *.2 and giving
a correct total for the two combined under *.1, and leaving a zero result for
the *.2 ?
Many thanks to you both again.

"RagDyeR" wrote:

You might try:

=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",A1:A8))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Graham" wrote in message
...
Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and
have
been ommited ? Something needs tweeking ?

"Graham" wrote:

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a
range
of numbers. The "COUNTIF" function in B1 will work for a whole number
as
shown below, but if i want the number of times .2 occurs, ignoring all
other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45

i am still interested in what is happening with the formula I gave you.
because of your response I assume the actual numbers you are using are not
the ones in the listing.

Are you putting in this equation and then copying it down?

entered in one place with the appropriate range it should be counting all of
the .2s in A1:A8
I do not understand the how you got the 1 for the combined and 0 for the *.2
alone

Note: I think you should use RagDyeR's equation, I was not aware you could
use search on a number. It will give the correct answer whether the data was
text or number or a mix

"Graham" wrote:

Thank you both for your further input. Both formulae work fine with some test
data, but RagDyeR's works with the real data, so I will run with that! It is
all numeric data. bj's formula is ading together the *.1 & the *.2 and giving
a correct total for the two combined under *.1, and leaving a zero result for
the *.2 ?
Many thanks to you both again.

"RagDyeR" wrote:

You might try:

=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",A1:A8))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Graham" wrote in message
...
Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and
have
been ommited ? Something needs tweeking ?

"Graham" wrote:

Excellent, Many Thanks!

"bj" wrote:

countif is not really a good match for this purpose

=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2))
should do what you want



"Graham" wrote:

Hi, I want to count the occurence of the first decimal place only in a
range
of numbers. The "COUNTIF" function in B1 will work for a whole number
as
shown below, but if i want the number of times .2 occurs, ignoring all
other
intergers and decimal places. Can I adapt this function for this?

=COUNTIF(A1:A8,"4567.21")


A B
1 1234.35 1
2 2345.78
3 3456.98
4 4567.21
5 5678.23
6 6543.56
7 6754.82
8 3452.45