sum certain cells when the value is greater than zero

I have numeric values from columns A1 to E1 and I want to sum just the cells A1,C1,E1 and only the values that are greater than zero

A1 B1 C1 D1 E1
10 5 -5 20 10

The result should be 20 in this example

Any suggestions????

Hi chapysvp

You could use SUMIF:

Formula:

Please Login or Register  to view this content.

If you have three cells with 10, -15 and 10 and you choose to ignore negative numbers, how do you get an anser of 10?

What is in the other cells? How do you choose which cells to sum? Is it always the same three cells? Or others?


Regards, TMS

=sumif(a1:e1,">0")

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Try one of these...

If you will never insert new columns before the range:

=SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(A1:E1>0),A1:E1)

If you might insert new columns before the range:

=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1),2)=0),--(A1:E1>0),A1:E1)

You're welcome. Thanks for the feedback!