Sum

[Neil Pearce]

Dear all,

Qu. 1: I have a worksheet with over 200 columns. The columns' headings
repeat every four columns, e.g. Heading A, Heading B, Heading C, Heading D,
Heading A, Heading B...

I wish to add a total column at the end for each heading, =A2+E2+I2+M2+...
This is clealy a laborious and tedius method. Is there an easy way of
calculating this?


Qu. 2: the IS forumla can be used to check cells for their specifics, e.g.
ISNUMBER(). Further is there a means of distinguishing if the cell is an
integer, e.g. ISINTEGER()? If not any ideas how I would be able to do this?

Thanking-you.

Cheers,

Neil

 

[Dave Peterson]

The easy one first:

=a1=int(a1)
will return true if A1 is an integer.
(Do you have to check for numeric, too?)

=and(isnumber(a1),a1=int(a1))

The other one:
=sumif($a$1:$Gr$1,"Heading A",$a2:$gr2)

 

[Pete_UK]

Q1: This will give you the sum of A2, E2, I2 etc up to IA2:

=SUMPRODUCT(--(MOD((COLUMN(A1:IA1)-1),4)=0),A2:IA2)

Q2: You can test for integer like this:

=IF(A1=INT(A1),"Integer","Not integer")

Hope this helps.

Pete

 

[Neil Pearce]

Thanks Dave,

The other problem I've encountered is...

Cell A1: "Number of rooms"
Cell B1: the number of rooms
Cell C1: "Total room areas"
Cell D1: the total area of all rooms

Again these columns repeat for over 200 columns repeating every four columns.

How would I go about summing the total number of rooms for all houses, i.e.
B1, F1, J1...

Trying the same idea you provided I thought the below might work but
apparently not!

=SUMIF($A$1:$GR$1,OFFSET(($A$1:$GR$1),0,-1,1,1)="Number of Rooms",$A$1:$GR$1)

 

[Neil Pearce]

Thanks Pete. Your solution answered my query before I even asked it!

 

[Dave Peterson]

You can change the formula so that it's offset:
=sumif($a$1:$Gr$1,"Number Of Rooms",$b2:$gs2)

Notice how the formula looks in A1 for the header, but sums the value in B2.

 

[Dave Peterson]

I wouldn't share a workbook that uses a formula based on the column or row
number. Too many things can go wrong--inserting or deleting a column can have a
very bad effect.

And I would be very hesitant to use it myself.