SUM only cells that contain odd numbers.

**dzugan** · 10-10-2017, 07:29 PM

Say I have a column of numbers and I want to add up only the odd numbers. I tried SUMIF(A1:A10,"ISODD",A1:A10) to no avail.

But, curiously, if I altered the formula to SUMIF(A1:A10,">5",A1:A10) it did in fact add up only the numbers that were > 5.

The syntax seems the same to me, so why the difference?

So then I tried =SUMPRODUCT(--ISODD(A1:A10),A1:A10) also to no avail.

But VERY curiously, the following formula did work to add up just the odd rows =SUMPRODUCT(--ISODD(ROW(A1:A10)),A1:A10)

Can someone explain (in simple terms, please) what concept(s) I'm missing? I don't want to just ask someone to write the formula for me, I want to understand why it works. Thanks!

**jeffreybrown** · 10-10-2017, 07:50 PM

dzugan,

With your first try, you can't put a function inside of a function the way you did.

The best way to understand how as formula works is to place your cursor of the formula and then Evaluate.

Evaluate Formula

Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

Here's a discussion you might find interesting to help in your learning https://www.excelbanter.com/excel-di...d-numbers.html

=SUMPRODUCT(--(A1:A10),--(MOD(ROW(A1:A10),2)=1))

Again, if you evaluate the formula above that will explain what's happening much quicker than trying to type out all of the steps.

I hope this helps...

**Root_** · 10-10-2017, 11:37 PM

Some Excel functions do not work with ranges, but they accept arrays.

ISODD is one of them. That's why it works with ROW(A1:A10).

For your formula to work, just add +0:

=SUMPRODUCT(--ISODD(A1:A10+0),A1:A10)

**dzugan** · 10-11-2017, 04:02 PM

Is there any kind of listing of which functions accept what? When I look under the alphabetical listing of Excel functions on the Microsoft website it doesn't seem to explain this.

https://support.office.com/en-us/art...rs=en-US&ad=US

Thanks

**alansidman** · 10-11-2017, 04:12 PM

Maybe this will be helpful: https://www.techonthenet.com/excel/f...ndex_alpha.php

**AlKey** · 10-11-2017, 04:12 PM

Here is another one

Formula:

Please Login or Register  to view this content.

**dzugan** · 10-11-2017, 04:15 PM

Hi,

I found a nice shortcut to evaluating a formula with F9. I guess my disappointment is that the evaluation only shows WHERE my problem is. This is of course useful info, but it still doesn't tell me WHY it doesn't work or HOW to fix it. Looking at Root's explanation below, it looks like I need more study on the characteristics of the ISODD function and the differences between ranges and arrays. Study on!

Thanks