# Total of all odd cells minus total of all even cells

#### simontwn

##### New Member
There is a number in each cell from A1, B1, C1 to Y1. In Z1, I need a formula to equal to the Total of (A1+C1+E1...) minus (B1+D1+F1....). Thank you very much

Simon in Monterey

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Richard Schollar

##### MrExcel MVP
Hi Simon

Try:

Code:
``=SUMPRODUCT(--(MOD(ROW(1:25),2)=1),TRANSPOSE(A1:Y1))-SUMPRODUCT(--(MOD(ROW(1:25),2)=0),TRANSPOSE(A1:Y1))``

which must be array-entered (Ctrl+Shift+Enter) - Excel will surround with {} braces if it has worked.

Best regards

Richard

#### Brian from Maui

##### MrExcel MVP
Try this, although not very dynamic or robust,

=SUMPRODUCT(MOD(COLUMN(A1:X1),2),A1:X1)-SUMPRODUCT(MOD(COLUMN(B1:W1),2),B1:W1)

Hey Richard.....

#### Richard Schollar

##### MrExcel MVP
Hey Brian

How stupid do I sound: I was struggling to figure out how to get numbers out of the COLUMN function - COLUMN(1:25) just didn't work!!!

D'Oh!!!

#### simontwn

##### New Member
I have tried both

Richard & Brian,

I tried yours both. Richard's shows "\$Value!" and Brian's shows the number in cell A1. Maybe I did not copy/paste properly. I will keep on trying. Thank you two.

Simon

##### MrExcel MVP
Z1:

=SUMPRODUCT(--(MOD(COLUMN(A1:Y1)-COLUMN(A1)+0,2)=0),A1:Y1)-SUMPRODUCT(--(MOD(COLUMN(A1:Y1)-COLUMN(A1)+1,2)=0),A1:Y1)

#### opinsky

##### New Member
may i ask one out-of-topic question:

what is the function of "--" in excel formulas?

Regards... OP

#### simontwn

##### New Member
Z1:

It works. Thank you very much.

#### Richard Schollar

##### MrExcel MVP
Simon

I tried yours both. Richard's shows "\$Value!"

I appreciate Aladin's formula works as required and is simpler than the one I posted, but mine should work. It will however return #VALUE if you don't array enter (ie instead of simply pressing Enter once typed in, you need to hold down Ctrl and Shift and then, whilst holding these down, press Enter). Excel will surround the formula with {} braces if it has worked.

Richard

Replies
6
Views
233
Replies
10
Views
465
Replies
1
Views
373
Replies
5
Views
339
Replies
1
Views
61

1,171,178
Messages
5,874,185
Members
433,033
Latest member
thatmacroguy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back