Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
I have quite a large excel sheet with about 210 columns. I need a sum at the end of each row for every third cell. I have tried a couple different formulas that should work, but they always end up adding other values in for some reason. The cells I need added go like so: E3,H3......HA3. I'm sure a few of you out there have dealt with this before. If you have a resolution, I would definately appreciate your response. -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
Hi!
Try this: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3),3)=2),E3:HA3) If you might ever insert new columns before the start of the range: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3)-COLUMN(E3),3)=0),E3:HA) Biff "bthieson" wrote in message ... I have quite a large excel sheet with about 210 columns. I need a sum at the end of each row for every third cell. I have tried a couple different formulas that should work, but they always end up adding other values in for some reason. The cells I need added go like so: E3,H3......HA3. I'm sure a few of you out there have dealt with this before. If you have a resolution, I would definately appreciate your response. -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
Try some formula like this:
(array formula, ending with Ctrl+Shift+Enter) =SUM(IF((MOD(ROW($A$1:$A$999),3)=0),$A$1:$A$999)) -- Best regards, --- Yongjun CHEN ================================= XLDataSoft - Data Analysis Expert, Excel/VBA Specialist - - - - www.XLDataSoft.com - - - - Free Excel-Based Data Processing Tool is Available for Download Free Excel / VBA Training Materials is Available for Download ================================= "bthieson" wrote in message ... I have quite a large excel sheet with about 210 columns. I need a sum at the end of each row for every third cell. I have tried a couple different formulas that should work, but they always end up adding other values in for some reason. The cells I need added go like so: E3,H3......HA3. I'm sure a few of you out there have dealt with this before. If you have a resolution, I would definately appreciate your response. -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
Okay I ended up using the first one and it worked perfectly. I have a second totalling column on the end right next to the cell where I put this formula. I assumed it would apply exactly the same, but it definately does not. I need to total every column cell starting at F3:HB3 also. I don't understand why this wouldn't work exactly the same. If you have an answer, I would definately appreciate it. -Ben Thieson Biff Wrote: Hi! Try this: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3),3)=2),E3:HA3) If you might ever insert new columns before the start of the range: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3)-COLUMN(E3),3)=0),E3:HA) Biff "bthieson" wrote in message ... I have quite a large excel sheet with about 210 columns. I need a sum at the end of each row for every third cell. I have tried a couple different formulas that should work, but they always end up adding other values in for some reason. The cells I need added go like so: E3,H3......HA3. I'm sure a few of you out there have dealt with this before. If you have a resolution, I would definately appreciate your response. -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
I need to total every column cell starting at F3:HB3 also.
Every column? F3,G3,H3,I3,J3,K3,L3........HB3 ? =SUM(F3:HB3) The other formula you wanted only every 3rd column to be summed. If that's also what you want with this new formula: sum every 3rd column from F3 to HB3: =SUMPRODUCT(--(MOD(COLUMN(F3:HB3),3)=0),F3:HB3) I don't understand why this wouldn't work exactly the same. The technique is the same but the condition is different because the range is different. The formula tests the column numbers to see if that particular column should be included in the sum. F3 = column() = 6 G3 = column() = 7 H3 = column() = 8 I3 = column() = 9 Using the MOD function we then need to find a divisor that returns a specific value and this establishes a pattern that we can take advantage of. MOD(COLUMN(F3),3) = 0 MOD(COLUMN(G3),3) = 1 MOD(COLUMN(H3),3) = 2 MOD(COLUMN(I3),3) = 0 So, we're telling the formula to sum those columns where the column number returns a MOD of 0 when the divisor is 3. Biff "bthieson" wrote in message ... Okay I ended up using the first one and it worked perfectly. I have a second totalling column on the end right next to the cell where I put this formula. I assumed it would apply exactly the same, but it definately does not. I need to total every column cell starting at F3:HB3 also. I don't understand why this wouldn't work exactly the same. If you have an answer, I would definately appreciate it. -Ben Thieson Biff Wrote: Hi! Try this: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3),3)=2),E3:HA3) If you might ever insert new columns before the start of the range: =SUMPRODUCT(--(MOD(COLUMN(E3:HA3)-COLUMN(E3),3)=0),E3:HA) Biff "bthieson" wrote in message ... I have quite a large excel sheet with about 210 columns. I need a sum at the end of each row for every third cell. I have tried a couple different formulas that should work, but they always end up adding other values in for some reason. The cells I need added go like so: E3,H3......HA3. I'm sure a few of you out there have dealt with this before. If you have a resolution, I would definately appreciate your response. -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
Okay it works, but now I need to understand why. In the first formula we used, the mod() had to equal 2, and in the second it had to equal 0. Now I had a formula that I was trying before and I was always using equal to 0, why would it be 2 in the case of the first formula? I assumed that because I was looking for every third column starting at the point I did, that would mean I would want to mod() 3 and search for a 0. If you wouldn't mind explaining the 2 to me, I would definately appreciate it. The other thing I didn't understand in the formula, was the (--)? What does that do. In my initial formula, I was using an if statement where you had the (--). What is it? -Ben -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing every third cell
I'm not sure I can explain it any better than I already have:
The formula tests the column numbers to see if that particular column should be included in the sum. F3 = column() = 6 G3 = column() = 7 H3 = column() = 8 I3 = column() = 9 Using the MOD function we then need to find a divisor that returns a specific value and this establishes a pattern that we can take advantage of. MOD(COLUMN(F3),3) = 0 MOD(COLUMN(G3),3) = 1 MOD(COLUMN(H3),3) = 2 MOD(COLUMN(I3),3) = 0 So, we're telling the formula to sum those columns where the column number returns a MOD of 0 when the divisor is 3. The cell interval really doesn't have anything to do with it. Every other cell, every 3rd cell, every 10th cell. It's the actual range that you need to know, specifically, the column (or row) numbers. What you need to do is compare the MOD of the column (or row) numbers and experiment with the divisor until you find a good pattern. Sometimes it's not so easy! The first formula started in column E (5) and the second formula started in column F (6) so a MOD with the same divisor would be different. About the "--", see: http://mcgimpsey.com/excel/formulae/doubleneg.html http://www.xldynamic.com/source/xld.SUMPRODUCT.html Biff "bthieson" wrote in message ... Okay it works, but now I need to understand why. In the first formula we used, the mod() had to equal 2, and in the second it had to equal 0. Now I had a formula that I was trying before and I was always using equal to 0, why would it be 2 in the case of the first formula? I assumed that because I was looking for every third column starting at the point I did, that would mean I would want to mod() 3 and search for a 0. If you wouldn't mind explaining the 2 to me, I would definately appreciate it. The other thing I didn't understand in the formula, was the (--)? What does that do. In my initial formula, I was using an if statement where you had the (--). What is it? -Ben -- bthieson ------------------------------------------------------------------------ bthieson's Profile: http://www.excelforum.com/member.php...o&userid=34357 View this thread: http://www.excelforum.com/showthread...hreadid=541295 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |