combine countif and mod functions

[thebutlerdidit]

I want to count the number of entries in a column of numbers that are not evenly divisible by 0.25. I'm unsuccessfully trying:

=COUNTIF(MOD(H2:H26,0.25,<>0))

Any pointers?

[Pete_UK]

Try it this way:

=SUMPRODUCT(--(MOD(4*H2:H26,4)=0))

I've multiplied the array by 4 and used 4 as the divisor, as dividing by fractions may give rounding errors.

Hope this helps.

Pete

[thebutlerdidit]

Thanks for quick reply. I'm still getting notice: "The formula you typed contains an error."

[Pete_UK]

Don't type the formula, then - instead, highlight it on the post above, then CTRL-C, then click onto the Excel window, select the cell that is to contain the formula, click into the Formula bar and highlight the contents (if any), then CTRL-V and press Enter.

Hope this helps.

Pete

[daddylonglegs]

=SUMPRODUCT(--(MOD(4*H2:H26,4)=0))

Wouldn't that only count integers?

I think it should be:

=SUMPRODUCT(--(MOD(4*H2:H26,1)=0))

or to avoid counting blank cells you might want this version

=SUMPRODUCT((H2:H26<>"")*(MOD(4*H2:H26,1)=0))

[thebutlerdidit]

None of those is working. I probably haven't made my objective clear. Column H has a bunch of numbers in it. I want to know how many of those numbers are not evenly divisible by 0.25. For example, if Column H has the following numbers in it:
2.25
3.75
4.00
3.9
3.5
3.3
I want the answer to be 2 because there are 2 numbers (3.9 and 3.3) that are not evenly divisible by 0.25. Anyone care to try again?

[daddylonglegs]

My modification to Pete's formula is counting the values which are divisible by 0.25......but if you want the opposite just change the = to <>, i.e.

=SUMPRODUCT(--(MOD(4*H2:H26,1)<>0))

I get 2 with that formula and your data

[newdoverman]

Try this:

Please Login or Register  to view this content.

[thebutlerdidit]

Yay! Thank you! This works like a charm!

[thebutlerdidit]

I should make it clear that Daddylonglegs' formula:

=SUMPRODUCT(--(MOD(4*H2:H26,1)<>0))

is what worked for me. Thanks again!!!

[thebutlerdidit]

OK, last note. I just tried newdoverman's solution (below) and it worked, too! Thank you both!

=SUMPRODUCT(--(MOD(A1:A6,0.25)>0))