Adding cells in every other column

[MarkT]

Hello,

I am using Excel 2007 with Win XP.

I am trying to create a current inventory count in column B of each row of
items. The count as inventory comes in or goes out will be located on the
same row, in every other column starting in column H. In other words the
count will be in cells H7, J7, L7, N7 and so on. There is no ending column -
no definate place the count will stop. It will be hundreds of columns, so I
don't want to do a formula such as

=sum(H7+J7+L7 .....etc)

I would like this in a formula format so that I can drag the formula down in
the spreadsheet to each row for each item that I am tracking.

Is there a way to sum specific columns other than the formula above?

Thanks again for everyone's help!

Mark

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(MOD(COLUMN(7:7),2)=1),7:7)

 

[MarkT]

It comes up with 0 (zero); and a circular reference error.

 

[Dave Peterson]

=SUMPRODUCT(--(MOD(COLUMN(H7:IV7),2)=1),H7:IV7)

You could use the column in which your data ends instead of IV, too.

 

[MarkT]

Awesome Dave, that worked!

I appreciate it very much. After seven years of working with excel you
would think I would know/remember these things....

Thanks again.

Mark

 

[MarkT]

Dave, one more quick question. How would I change your formula to do the
same thing, but have it add up columns G,I,k etc, in other words one column
before the h column - as in your formula provided. I tried to change your
formula from H7 to G7 but I get the same result? Your formula starts with
the seventh column and adds ever other column. I also need to have a formula
that starts in the sixth column and adds every other column thereafter. I'm
confused (still).

Thanks,

Mark

 

[Dave Peterson]

This kind of formula that depends on the column number that contains the value
always scares me. If columns are inserted/deleted, it could break the
formula--or even worse, the formula may work--but just show a value that isn't
what you really want.

I think I'd add a row (and hide it later).

Then use an indicator in that row.

=sumif($h$1:$iv$1,"$",$h7:$iv7)

 

[Dave Peterson]

=SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=0),g7:IV7)

The =mod(..., 2) will return a 0 or 1. 1 means you're in an odd numbered
column. 0 means you're in an even numbered column.

But I'm confused about what you want. Column G is column 7. That's an odd
number.

You could add the odd numbered columns starting with G:
=SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=1),g7:IV7)

You could add the even numbered columns starting with G:
=SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=0),g7:IV7)

====
I still like the (hidden) row of indicators and =sumif().

 

[MarkT]

Sorry for the confusion, my fault, I changed my spreadsheet around a bit
inbetween postings, I moved some columns around.

I always try to learn something when I get stuck, that's why I appreicate
everyone's response to my various questions.

What would happen if I wanted to count every third column? Is this possible
since with the formula result being true or false? True being even, false
being odd?

As far as the hidden row, I understand your concern, but I have hundres of
rows of inventory items, so I would have to have hundreds of rows of
indicators. It's shouldn't be a problem with adding rows, that side of the
spreasheet is fairly basic and I don't see any changes ever being made.

Whenever I make a change to a spreasheet like that, I usually start over
from scratch and do things differently, more efficiently, etc.

In any event, again, thanks very much for your help I appreicate it very much.

 

[Dave Peterson]

You wouldn't need hundreds of rows of indicators. You'd just check the single
row in the formula.

Notice the $ signs around $h$1:$iv$1 in this formula:
=sumif($h$1:$iv$1,"$",$h7:$iv7)

That means that this range won't change when you copy the formula to other
locations.

=mod(...,2)
will return 0 or 1
=mod(...,3)
will return 0, 1, or 2
=mod(...,18)
will return whole numbers between 0 and 17.

So you can change this portion:
MOD(COLUMN(g7:IV7),2)=0
to
MOD(COLUMN(g7:IV7),3)=0 (or 1 or 2--depending on what column you want)

Sorry for the confusion, my fault, I changed my spreadsheet around a bit
inbetween postings, I moved some columns around.

I always try to learn something when I get stuck, that's why I appreicate
everyone's response to my various questions.

What would happen if I wanted to count every third column? Is this possible
since with the formula result being true or false? True being even, false
being odd?

As far as the hidden row, I understand your concern, but I have hundres of
rows of inventory items, so I would have to have hundreds of rows of
indicators. It's shouldn't be a problem with adding rows, that side of the
spreasheet is fairly basic and I don't see any changes ever being made.

Whenever I make a change to a spreasheet like that, I usually start over
from scratch and do things differently, more efficiently, etc.

In any event, again, thanks very much for your help I appreicate it very much.

 

[MarkT]

Thanks Dave,

That makes perfect sense to me know. I assumed each inventory row would
need an indicator row since there would be multiple entries on each row from
various columns used.

The MOD function is a nice little tool; I will be able to use that in
another project that I will be working on soon.

Thanks again!

Mark