Select Page

If we have to count only odd or even numbers from a data list in excel, we have a number of options available as discussed here:

If possible add an extra column just beside the data list you want to count. Suppose data is in column “C” and you have entered a new column at “D”, if it is not already blank. Now, insert the following formula in column “D”.




In both the cases you will either get “TRUE” or “FALSE”. If you have applied 1st formula you will get “TRUE”, if number is Odd and “FALSE” if number is Even.  And vice-versa

Now, by applying filter you can count “TRUE” or “FALSE” to get required result.

But, what if you can’t add any column? Yes, still you can count the Odd or Even numbers in a single cell using formula mentioned here.


To count Odd numbers: =SUMPRODUCT((MOD(DATA,2)<>0)*1)


To count Even numbers: =SUMPRODUCT((MOD(DATA,2)=0)*1)


“Data” is the given name of range, containing data to be counted.


In case you have any query regarding above, you may send the same as comment to this blog.

“Anyone who has never made a mistake has never tried anything new.”

– Albert Einstein

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!

Pin It on Pinterest

Share This