How do I add every few cells

How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other 
cells such as B6 and B9 have other forumlas and info that is not to be 
included).  I did this manually but the formula has now gotten so long that I 
get an error saying "formula too long".  So I figured, there must be a 
shorter formula that just adds every 3 (or whatever designated) cells 
starting with one cell and ending with another.
0
SusanK (6)
8/2/2005 8:22:02 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
268 Views

Similar Articles

[PageSpeed] 31

Try this:

=SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500))
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"SusanK" <SusanK@discussions.microsoft.com> wrote in message
news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
> How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
> cells such as B6 and B9 have other forumlas and info that is not to be
> included).  I did this manually but the formula has now gotten so long
that I
> get an error saying "formula too long".  So I figured, there must be a
> shorter formula that just adds every 3 (or whatever designated) cells
> starting with one cell and ending with another.

0
ragdyer1 (4060)
8/2/2005 8:34:06 PM
Try this Susan

=SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0))

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"SusanK" <SusanK@discussions.microsoft.com> wrote in message news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
> How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
> cells such as B6 and B9 have other forumlas and info that is not to be
> included).  I did this manually but the formula has now gotten so long that I
> get an error saying "formula too long".  So I figured, there must be a
> shorter formula that just adds every 3 (or whatever designated) cells
> starting with one cell and ending with another. 


0
rondebruin (3790)
8/2/2005 8:37:40 PM
I tried this and it said that it couldn't creat because of a circular 
reference.  This formula I need provides me with a number that is used in a 
formula for cells B8, B11,  etc.  Any other suggestions?  I'd really 
appreciate it!!!  

"RagDyer" wrote:

> Try this:
> 
> =SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500))
> -- 
> HTH,
> 
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
> 
> 
> 
> "SusanK" <SusanK@discussions.microsoft.com> wrote in message
> news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
> > How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
> > cells such as B6 and B9 have other forumlas and info that is not to be
> > included).  I did this manually but the formula has now gotten so long
> that I
> > get an error saying "formula too long".  So I figured, there must be a
> > shorter formula that just adds every 3 (or whatever designated) cells
> > starting with one cell and ending with another.
> 
> 
0
SusanK (6)
8/2/2005 8:55:07 PM
Ron, I got a message saying that it wouldn't work because of a circular 
reference.  Cells like B8 and B11 have formulas that are based on the outcome 
of this formula I'm tring to create.  Any possible suggestions?? It would be 
much appreciated! - Thanks!

"Ron de Bruin" wrote:

> Try this Susan
> 
> =SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0))
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "SusanK" <SusanK@discussions.microsoft.com> wrote in message news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
> > How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
> > cells such as B6 and B9 have other forumlas and info that is not to be
> > included).  I did this manually but the formula has now gotten so long that I
> > get an error saying "formula too long".  So I figured, there must be a
> > shorter formula that just adds every 3 (or whatever designated) cells
> > starting with one cell and ending with another. 
> 
> 
> 
0
SusanK (6)
8/2/2005 8:59:10 PM
Then eliminate those 2 cells from the formula, and add them separately:

=SUMPRODUCT((MOD(ROW(B13:B500),3)=1)*(B13:B500))+B7+B10
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"SusanK" <SusanK@discussions.microsoft.com> wrote in message
news:E0969D02-6AF4-4D0A-8ADC-8500DDC19CD9@microsoft.com...
> I tried this and it said that it couldn't creat because of a circular
> reference.  This formula I need provides me with a number that is used in
a
> formula for cells B8, B11,  etc.  Any other suggestions?  I'd really
> appreciate it!!!
>
> "RagDyer" wrote:
>
> > Try this:
> >
> > =SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500))
> > -- 
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> >
> > "SusanK" <SusanK@discussions.microsoft.com> wrote in message
> > news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
> > > How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The
other
> > > cells such as B6 and B9 have other forumlas and info that is not to be
> > > included).  I did this manually but the formula has now gotten so long
> > that I
> > > get an error saying "formula too long".  So I figured, there must be a
> > > shorter formula that just adds every 3 (or whatever designated) cells
> > > starting with one cell and ending with another.
> >
> >

0
ragdyer1 (4060)
8/2/2005 9:13:02 PM
Glad you got it working
Thanks for your private mail Susan

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"SusanK" <SusanK@discussions.microsoft.com> wrote in message news:517768E3-D952-4A39-A92F-BA8BA7D7C655@microsoft.com...
> Ron, I got a message saying that it wouldn't work because of a circular
> reference.  Cells like B8 and B11 have formulas that are based on the outcome
> of this formula I'm tring to create.  Any possible suggestions?? It would be
> much appreciated! - Thanks!
>
> "Ron de Bruin" wrote:
>
>> Try this Susan
>>
>> =SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0))
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "SusanK" <SusanK@discussions.microsoft.com> wrote in message news:3D5718F9-76F0-4304-9B49-72E131662721@microsoft.com...
>> > How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
>> > cells such as B6 and B9 have other forumlas and info that is not to be
>> > included).  I did this manually but the formula has now gotten so long that I
>> > get an error saying "formula too long".  So I figured, there must be a
>> > shorter formula that just adds every 3 (or whatever designated) cells
>> > starting with one cell and ending with another.
>>
>>
>> 


0
rondebruin (3790)
8/2/2005 9:54:28 PM
You could use a helper column and sumif function.  Put a character in the 
helper column in each row you want to include in the sum.  If you put as s in 
each applicable row of Col A, your formula would be =sumif 
(a7:A500,"S",b7.b500) 

"SusanK" wrote:

> How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other 
> cells such as B6 and B9 have other forumlas and info that is not to be 
> included).  I did this manually but the formula has now gotten so long that I 
> get an error saying "formula too long".  So I figured, there must be a 
> shorter formula that just adds every 3 (or whatever designated) cells 
> starting with one cell and ending with another.
0
eider (13)
8/2/2005 10:27:02 PM
Reply:

Similar Artilces:

Testing a range of cells in an IF FUNCTION
Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work Please help Aaro aaronplange at hotmail.com Hi maybe someth...

How to add 5 min to current time ()
Greeting, How to add 5 min to current time ()?? On Tue, 12 Feb 2008 21:23:00 -0800, ghost <ghost@discussions.microsoft.com> wrote: >Greeting, >How to add 5 min to current time ()?? DateAdd("n", 5, Now()) or for just a time value - actually a date/time value on December 30, 1899 - DateAdd("n", 5, Time()) -- John W. Vinson [MVP] ...

EzPaste-xl2ppt Excel add-in, automating the copy/paste of charts and data
Hello, This is to notify about the release of EzPaste (http:// www.EzPaste.net), an Excel add-in intended to completely automate the transfer of charts and data from Excel to PowerPoint. EzPaste identifies automatically all the charts and the tables defined in the active workbook. The user then selects which of them he wants to paste to PowerPoint=AE , and EzPaste does all the work, would you have to paste one or one hundred tables/charts at once Even if you have to paste the selected range or chart, EzPaste does it with the click of a button Sorry if this message is a bit advertising, b...

Trendlines to ignore empty cells
Hi I have five years of data and the x axis is set for 6 years to allow the chart to fill up as new data is input. When I add a trendline it seems to include these empty cells. In fact, the cells contain a formula but have managed to get the data plot to not be zero by using the NA() function which I found in another thread. Any ideas how I can get the trendline to only use the actual data for its calc rather than plotting along the whole of the x axis? Much appreciate your advice Keith This example will allow you to have a dynamic linear trend line that recalculates and moves with the...

assign cell content as file name
I am attempting to create a macro that will save a workbook with the file name under which I save the workbook coming from the contents of one of the cells in the workbook. Suppose that each time the workbook was altered, a specific cell (A1 on Sheet1 for instance) contained the desired file name for that workbook. It is my goal to create a macro that automatically assigns said workbook a name based on the content of that cell. Any help would be most appreciated. Something like??? dim myFileName as string myfilename = "C:\temp\" & thisworkbook.worksheets("sheet1"...

spell check does not visually cycle through cells when run from macro?
I'm trying to recreate the same behavior in a macro that spellcheck has when the spellcheck button is pressed. The spellcheck does not cycle through the cells visually when run from a macro, so when there is a misspelled word, you cannot see the cell it is a part of. If anyone knows how I can get this behavior to work, it would be GREATLY appreciated. I did a record new macro, and got code similar to the following: Sub testSpelling() Range("F2:F500").Select Selection.CheckSpelling SpellLang:=1033 End Sub thanks, dan Dan, Add the fiollowing to your code as shown: ...

cannot add to archive.pst
A few months ago, I manually archived a few folders of my inbox and it worked great. I verified that the archive was there, (open outlook data file) and the archive data was in my folder list. I just tried to archive more data and it does not work. I do not get any error messages, outlook seems like it is going through the process ( a little quickly) but the files do not move. They are still in the original folders, not in archive. archive.pst did not change in size. How can I correct this? Using Outlook 2003. Archive depends on the date settings you have set, and this date is t...

displyaing the referenced cells ?
Hi everyone ! Working on an excel file full of references, it would be very useful to be able to make the display to jump directly to the cells referenced by the active cells I currently see. So if my cell TU789 reference the cell JI890, I don't want to search for JI 890, but would like excel to automatically show me this cell. Do you know if it is possible ? Is there a shortcut for that ? Thanks, JR. It would be difficult to do that generically, as the cell could contain a formula that references several other cells - which one should be chosen in this case? You might think about using ...

Formatting cells from internet
I have copied and pasted a chart from a website. (The website will not let me import) The cells are showing numbered values, but when i go to put the formula in it is giving me a zero like it cannot read the numbers in the cell. That is on 3 seperate columns. On the very last column it is reading the numbers I cannot make it recognize that those columns are numbers to be used in formulas Thanks for your help! Use Dave McRitchie's Trimall macro to clean up the data. Then copy an empty cell, select all your data and do Edit / PasteSpecial / Add and it will coerce all data back to nu...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

Is it possible to add a "View" button under the purchase order section?
Would be nice to have the abilty to allow someone to be able to view the purchase order as opposed to the given "new, delete, edit, or receive" buttons. This way someone who needed the information and did not have the appropriate security would not be able to delete or change the PO. I tried to CTL S the new, delete, edit and receive buttons, but that basically locks them out completely. Is this something that may be possible? You can make excel pivot table so any employee can view that worksheet from their terminal. This way you can define the fields you want to show as well ...

Highlighting A Cell
Does anyone know how to do a formula that says if a cell equals the wor FALSE then highlight this particular cell gray? Example: Typing in Cell A2 IF A1 = FALSE then gray otherwise white I tried to do it in Conditioning Formatting but can't figure it out t reference another cell. Any help would be greatly appreciated -- Coltsfa ----------------------------------------------------------------------- Coltsfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1998 View this thread: http://www.excelforum.com/showthread.php?threadid=46898 Good evening Colt...

Make a cell flash
Please help I to make cell b1 flash when it becomes greater than 5 See: http://www.cpearson.com/excel/BlinkingText.htm -- Gary's Student "Lloyd" wrote: > Please help I to make cell b1 flash when it becomes greater than 5 see response in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Lloyd" <Lloyd@discussions.microsoft.com> wrote in message news:08B5FB85-6AAB-4B7E-ABC1-8CC8E2B61466@microsoft.com... > Please help I to make cell b1 flash when it becomes greater than 5 Thanks for the link. I ...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

How do I get automatic completion to work for an in-cell dropdown
If I have a list of Alpha, Beta, Gamma and an in-cell dropdown, I'd like typing 'G' to auto-select Gamma. I'm pretty sure it was doing that at some point, but now, it does not. This isn't supported under data|validation. You could use A,B,G in the dropdown and then use a helper cell with a formula in it, though. Say you put your abbreviations in A1:A3 of sheet2 and the real names in B1:B3, then you could use: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) Joseph Weisblatt wrote: > > If I have a list of Alpha, Beta, Gamma and an in-cell dropd...

How did you add text into publisher, without using boxes?
how do you add text to publisher without using text boxes I suppose you could create your text as an image and insert the image into your publisher file. -- Don Vancouver, USA "Calvin Scott" <Calvin Scott@discussions.microsoft.com> wrote in message news:64D23D52-138D-47B4-B265-4A41BF14BF55@microsoft.com... > how do you add text to publisher without using text boxes Calvin Scott <Calvin Scott@discussions.microsoft.com> was very recently heard to utter: > how do you add text to publisher without using text boxes You don't. Text in Publisher has to e...

Add range options to Rec Dist report
Add additonal range options to the Receivings Distribution report - only by Audit Trail is allowed ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=b0268d6b-4b6a-4805-9b52-e...

access a cell in a range
I have variables: dim myrange as Range dim myString as String myrange specifies a specific column. How can I specify the cell in row 1 of myRange so I can set it's contents to myString? Hi there keyser soze, (Fan of the movie, eh? ;) <g> ) Like so .. myString = myRange(1).Value HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) <keyser_Soze@usa.com> wrote in message news:1128697509.320303.12550@g49g2000cwa.googlegroups.com... >I have variables: > > dim myrange as Range > dim myString as String > > myrange specifies a specific ...

Averaging cell's...problems with Div/0
Hi guys. First time poster here so be gentle with me. I am looking fo some assistance averaging a range of 1 to 3 numbers. Here is what I have so far. =(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)) This works great. What it does is checks to see if there is a value i the cell, then counts it and divides by the right number. I.E if yo only have two values out of 3 filled in it divides the number by tw instead of 3. My problem... if all 3 fields are 0 then I get a divide by 0 error. Any suggestion on how to fix this? I don't want my spreadsheet to loo messy before I start plu...

Moving from cell to cell
Moving from cell to cell using arrow key stopped working. Help! Maybe hit Scroll Lock key... If Scroll Lock is on, the arrows still work, but in a different way. HTH Regards, Howard "crys0814" <crys0814@discussions.microsoft.com> wrote in message news:1B985BCA-BFB8-4718-8ED8-833BE78D8895@microsoft.com... > Moving from cell to cell using arrow key stopped working. Help! Hi there cry0814, It is possible that you have accidentally pressed your scroll lock key which disables your arrow keys. Try pressing it again and let us know your results Regard...

Cannot add user to CRM
I have trouble adding user to the CRM. I have 50 users in AD, however, when it comes to adding user in CRM, I can only choose from 15 of them. I wonder why!? I suppose I should be able to pick from all 50 users whoever is in the AD, right? If anyone have any clue on this problem and give me some feedback, I would surely appreciate that!!! CRM deployment manager will not display certain categories of users - eg if they have been migrated from NT domains or Novel NDS without passwords. Search the group archives for UserAccountControl "Martijn van Halen" <MartijnvanHalen@d...

Two different cells
I have a file with more the 1000 names and addresses. My proble is that the firat and last names are in 2 differents cells. I i would like to put it in teh same cell. Is there anyway I csn do that? Please help too many names to re-type Carlos Assuming that Column A contains the first name and Column B the last name... =A1&" "&B1 OR =B1&", "&A1 ....depending on your preference. Hope this helps! In article <520FA0CA-1940-42E5-B84D-22BA15D59638@microsoft.com>, "Hombreck" <Hombreck@discussions.microsoft.com> wrote: > I have a...

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...