Count only cells ending with decimal value of .98

[paul.hobaica]

I have a field of data that can either end in .99 or .98. The integers however are varied.

Is there a way to only count the number of cells that end in .98? Something similar to 'COUNTIFS($A:$A,"*.98")' but this formula doesn't work due to the values being NUMBERS.

I am trying to avoid creating a new field in the data array if at all possible.

 

[Claus Busch]

Hi Paul,

Am Tue, 12 Nov 2013 08:40:52 -0800 (PST) schrieb (e-mail address removed):

I have a field of data that can either end in .99 or .98. The integers however are varied.

Is there a way to only count the number of cells that end in .98? Something similar to 'COUNTIFS($A:$A,"*.98")' but this formula doesn't work due to the values being NUMBERS.

try:
=SUMPRODUCT(--(MOD(A1:A1000,1)=0.98)*1)


Regards
Claus B.

 

[Paul Hobaica]

Hi Paul,



Am Tue, 12 Nov 2013 08:40:52 -0800 (PST) schrieb :






try:

=SUMPRODUCT(--(MOD(A1:A1000,1)=0.98)*1)





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks, I've tried that and it works, but I'm trying to use a COUNTIFS since there are multiple criteria (mostly text).

The full formula string appears as:

=COUNTIFS(table!$D:$D,$A3&"*",table!$D:$D,"*"&$B3,table!$L:$L,"*.98",table!$I:$I,"<>APPLE",table!$I:$I,"<>BANANA")

Obviously the table!$L:$L,"*.98" is the part that's not working as intended. Can I use a SUMPRODUCT function with all of the other criteria?

 

[Claus Busch]

Hi Paul,

Am Tue, 12 Nov 2013 08:53:56 -0800 (PST) schrieb Paul Hobaica:

The full formula string appears as:

=COUNTIFS(table!$D:$D,$A3&"*",table!$D:$D,"*"&$B3,table!$L:$L,"*.98",table!$I:$I,"<>APPLE",table!$I:$I,"<>BANANA")

try:
=SUMPRODUCT(--(ISNUMBER(SEARCH(A3,D11000))),--(ISNUMBER(SEARCH(B3,D11000))),--(MOD(L1:L1000,1)=0.98),--(I1:I1000<>"APPLE"),--(I1:I1000<>"BANANA"))


Regards
Claus B.

 

[Claus Busch]

Hi Paul,

Am Tue, 12 Nov 2013 18:22:32 +0100 schrieb Claus Busch:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A3,D11000))),--(ISNUMBER(SEARCH(B3,D11000))),--(MOD(L1:L1000,1)=0.98),--(I1:I1000<>"APPLE"),--(I1:I1000<>"BANANA"))

if the value you are looking for in column D is A3 & B3 you can change
--(ISNUMBER(SEARCH(A3,D11000))),--(ISNUMBER(SEARCH(B3,D11000)))
to
--(D110000=A3&B3)


Regards
Claus B.

 

[Ron Rosenfeld]

try:
=SUMPRODUCT(--(MOD(A1:A1000,1)=0.98)*1)

I would suggest =SUMPRODUCT(--(ROUND(MOD(A1:A10,1),2)=0.98)*1)

There's a problem with certain numbers because of the IEEE conventions.

e.g: mod(198.98,1) = approx: 0.97999999999998976818460505

and I'm sure there are others, also.