Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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) Neil Pearce wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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 On Nov 24, 7:05*pm, Neil Pearce wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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) "Dave Peterson" wrote: 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) Neil Pearce wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
Thanks Pete. Your solution answered my query before I even asked it!
"Neil Pearce" wrote: 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) "Dave Peterson" wrote: 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) Neil Pearce wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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. Neil Pearce wrote: 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) "Dave Peterson" wrote: 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) Neil Pearce wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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. Neil Pearce wrote: Thanks Pete. Your solution answered my query before I even asked it! "Neil Pearce" wrote: 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) "Dave Peterson" wrote: 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) Neil Pearce wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|