Ask Your Question

using isodd with countif

asked 2020-10-17 04:11:58 +0100

rootopia gravatar image

Hi, noob question, how do I use ISODD with COUNTIF? I want to provide a count of the number of odd (or even) numbers that occur in a range of numbers but I don't know how to use the ISODD function within the range used in COUNTIF e.g. something like =COUNTIF(A1:F1,ISODD(?)) what goes into (?) A1:F1 might contain 1,1,2,2,3,3 so the result should be 4 in this case. I realise I could set up an ISODD test for each cell then sum the results but was hoping for a more concise way of expressing as one formula? Cheers Greg

edit retag flag offensive close merge delete


Thank you Opaque! I haven't explored arrays as yet but it looks like they will open some additional functionality. Cheers, Greg

rootopia gravatar imagerootopia ( 2020-10-18 02:19:57 +0100 )edit

Hi @Opaque, I tried the third version and used the CTRL+SHIFT+ENTER as suggested and that worked ok but when I tried to replicate the cell down through more cells I got errors like 508 and "you can't modify part of an array", the first two versions worked fine and replicated ok but it seems that something different is happening in the third version, as it stands, I'm happy with either of the first two versions but was curious about the behaviour of the third one?. Cheers, Greg

rootopia gravatar imagerootopia ( 2020-10-18 02:42:47 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-10-17 11:20:50 +0100

Opaque gravatar image

updated 2020-10-17 11:27:06 +0100


=SUMPRODUCT(COUNT(IF(ISODD(A1:F1)))) --or array formula ---
{=COUNTIF(ISODD(A1:F1);"=1")} (Type =COUNTIF(ISODD(A1:F1);"=1") into the cell but use CTRL+SHIFT+ENTER, instead of ENTER only, to finalize the input)

will do.

Note SUMPRODUCT is used to force array evaluation (first and third solution in fact are the same thing).

Hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-17 04:11:58 +0100

Seen: 16 times

Last updated: Oct 17