How to you selectively sum on a series of numbers?

I have a column filled with numbers, but I only want to sum up a few of them.

Let's say I have numbers filling A1:A10.  Let's say I only want to sum up 
the numbers located is A3 and A4.  I'd like to ability to enter something in 
the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up 
the values in the "A" cells that have a corresponding "y" in the "B" column.  
This formula would allow me to selectively choose which cells needs to be 

What is the best/cleanest way to write that formula?  Thank you.
nospam7635 (44)
9/23/2004 3:11:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 44


this will add up all values in A where the value in B is "Y

Alex Delamai
Alex Delamain's Profile:
View this thread:

9/23/2004 3:26:31 PM
Thank you!

"Alex Delamain" wrote:

> =SUMPRODUCT((A1:A10)*(B1:B10="y"))
> this will add up all values in A where the value in B is "Y"
> -- 
> Alex Delamain
> ------------------------------------------------------------------------
> Alex Delamain's Profile:
> View this thread:
nospam7635 (44)
9/23/2004 4:05:04 PM

Frank Kabel
Frankfurt, Germany

"No Spam Please" <> schrieb im Newsbeitrag
> I have a column filled with numbers, but I only want to sum up a few
of them.
> Let's say I have numbers filling A1:A10.  Let's say I only want to
sum up
> the numbers located is A3 and A4.  I'd like to ability to enter
something in
> the b column (e.g. "y" in cells b3 and b4) and have a formula that
adds up
> the values in the "A" cells that have a corresponding "y" in the "B"
> This formula would allow me to selectively choose which cells needs
to be
> summed.
> What is the best/cleanest way to write that formula?  Thank you.

frank.kabel (11126)
9/23/2004 5:44:02 PM

Similar Artilces:

Non Numbers Cells
Hello, I would like to know if I can create a Macro that will do a search in column A (from A2 to A65536) and every row that has anything else than a number, delete the entire row (not just the cell). Thanks!!! Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If not isnumeric(Cells(row_index, 1).Value) then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub >-----Origi...

series of cells in a column #2
thanhyou so vey much -- ROLG ------------------------------------------------------------------------ ROLG's Profile: View this thread: ...

How to join auto number and letters
Hi, I have a form for issuing a letter number for our letter book. In this form , I have a text box with auto number . what I need is to issue auto number start with letters. For example: APP&P1, which 1 is the auto number and it is start with “APP&P”. once the user complete the letter subject, letter receive and the sender , will receive a control number or letter number. What about having two fields, where the first field contains the letter prefix and the second field contains the number? You could display the two fields in a calculated control combining the data. "...

series objects; finding specific ones in the collection
I asked this before but I'm still not clear on it. When ever I wish to create a new series, no big deal: Dim SeriesRandTarget As Series Set SeriesRandTarget = myChart.SeriesCollection.NewSeries As long as I have the object, I can use it. If I set the name attribute as in SeriesRandTarget .Name = "Hello" I can collect it later if needed by: Set myseries = myChart.SeriesCollection("Hello") But what about this? What is the new series name I just created in the below example? (The macro recorder has me in this fix!) myChart.SeriesCo...

Cutting a line in a line chart when data series stops
I have a series like so: Jan 10 Feb 10 Mar 11 Apr 12 May Jun Jul And so on - the rest of the year is blank. When I draw a line chart for the full 12 months the series draws a line up to April, then plummets to zero in May. I want the line to just stop at April. If I use a series as above and genuine empty cells for the end of the year this ok, but since my May cell reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a blank cell. Please help!! Change your formula to =IF($A11<=$D$17,G11,NA()) -- David Biddulph "DannyS" <Da...

Loop through columns and sum them at bottom
Hi, first time poster, so be kind. I've been working on coding a summary sheet that pulls data from worksheets in the active workbook. I'm almost done, but am stumped at writing some code that will loop through the columns that I have copied and total them. I recorded a macro, but nothing happened when I ran it. What I have now is this code snippet that I found which works perfectly when I put my cursor in the active cell. I am not sure how I should write it into my sub. code that I found that works: Set rng1 = ActiveCell.End(xlDown) Set rng2 = rng1.End(xlUp)(2, 1...

Deleting check numbers
I finally got online updates to match downloaded transactions with my manual entries. I did this by opening up a completely new file (starting pretty much from scratch), only way it would work. Now there are 2 new problems. One, when I "accept", it is replacing check numbers with "check", even though it keeps my manual payee name. And two, it keeps downloading the same transactions so I repeatedly have to accept them over and over again after each update. Any thoughts? Is this all still a part of the same Yodlee problems, or is there an adjustable setting that ...

count number 12-17-07
Is there a way to show a count of records with the same part numbers. Jeff Create a new query in design view. Add the table with the part numbers. Click on the Totals button (looks like a "sum of" button from Excel). Add the [PartNumber] field ... leave the aggregation as "GroupBy". Add the table's primary key field ... change the aggregation to "Count". Run it. Regards Jeff Boyce Microsoft Office/Access MVP "Jeff Klein" <jklein@nospam> wrote in message news:eKYDD6NQIHA.280@TK2MSFTNGP03.phx.gbl... > Is there a way to show a c...

Merging 2 excel sheets with numbers and words
I started with one sheet (using Excel 2003) but took a copy home (on a flash drive)to work on and added data (both numbers and "X" 's) - using Excel 2007. I saved the work done at home so Excel 2003 could "read" it. Then, I did some work on the copy my computer at work (BIG mistake!!) So, the problem is: I have some data on my flash drive copy which is not on the work computer sheet, and vice versa. I want to merge the 2 copies - so I cannot replace either one because data will be lost. Options??? Way to do this? (It seems like it will be easier to "Co...

How can I add values from several worksheets in a line chart seri.
I have an Excel document with 12 (monthly) worksheets. I want to make a line chart pulling data in one cell (same cell on each sheet) from each of the twelve sheets. In other words, Jan F2 + Feb F2 + Mar F2, etc. Can I do that on the chart's Source Data menu in the Series/VALUES field? If so, what is the proper language/formula. If not, what are my options? Do I have to make a total page and pull from it? Also, is it possible to make the Category (X) axis label be the Worksheet Name (Jan, Feb, Mar) Lastly, is there a place that gives sample formulas for someone trying to brush...

put numbers back on a calendar template after removing shading?
O n the Event Schedule Planner template, when I remove shading from the calendar the numbers are removed also. It won't let me put them back. When I use "no fill" it removes the numbers too. Exactly what template are you using? What version Publisher? -- Mary Sauer MSFT MVP news:// "dfarnelli" <> wrote in message > O > n the Event Schedule Planner template, when I remove shading from the...

Formula to see if a number is allocated
I need to find out if a number is allocated or free. Basically on sheet1 is a load of information. in Column G on this sheet is a unique identifier number. In sheet2 I have a list of numbers say from 1-100 which correspond to the identifier numbers in sheet1. Id like a forumla something like: "If A1 in sheet 2 = any number in column G in sheet1, then print Allocated, else print free" At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free") But it does not work. What it is doing is If A1 = everything in column G then say allocated, else say fre...

Combining Worksheet Numbers
And old problem revisited. I brought this question to this board about a year ago and got two spectacular answers from Tom Ogilvy and Ken Wright. See previous thread ---> I will repeat my question again just in case Anyway, I have the same problem/question, but this time instead of all of the worksheets having a static set of values (names), the players will be changing positions from "season" to "season." Thus, the Start to finish thing doesn't seem to work. I am doing a career statistical page for an online soccer simulation. I want to...

Formula for selecting a varying cell reference
I'm trying to tie 2 worksheets together. The first is a data entry sheet, set up by month. The second is a sheet that I want to pick up monthly data -- i.e., the most recent month of information entered on the data entry sheet. Is there a formula I can write in the monthly sheet that will look at the data entry sheet and go to the last monthly column that has data and retrieve that? I guess I'm thinking like a flowcharting yes/no process starting in December -- i.e.,: Is December blank? Yes -- go to November; No -- use this data -- etc., etc., etc. Any ideas? ...

Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a range of cells and will not release. I end up having to force Excel to close and restart. Any help would be greatly appreciated. It does it with most versions of Excel. Hello Check if the F8 key was not pressed (you would have the "EXT" mention on the Excel Status bar), if yes press F8 again. Apart from that I think this could be a mouse driver problem so to make sure try and update your mouse driver (esp if you have a wheel mouse). Apart from that? HTH Cordially Pascal "Wolfer50" <Wolfer...

Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with hyperlinks. I wish to "place" the column of hyperlinks behind the column of numbers, so if one clicks on the "number" the hyperlink will open. I know how to do this to each individual cell one at a time, but the columns have a couple thousand numbers and hyperlinks. Is there a way I can combine or merge the columns all at once to do what I want? Thank you for any response. Are your hyperlinks the result of the =HYPERLINK() function or are they Inserted hyperlinks?? -- Gary''s Stud...

Selecting Cells
Please help, When starting excel, I click on a cell and get the cross pointer but when I move the mouse to put a formula or data into that cell all cells are selected in the direction of mouse movement. I am unable to de-select the cells and the only thing I can do is close excel from the task bar. I have come across this problem once before But cannot remember how to solve it. Thanks in advance for any help with this issue. Regards Paul Paul You sould only get the cross hair if you move the mouse over the box at the bottom right-hand of the cell. This is used to enter a fill of a s...

Invert selection in List Control
Hi all, I'm trying to invert selection (i.e. select unselected and unselect selected items) in a multiple-selection List Control in report mode. I tried the following void GetTLETab::OnBnClickedButton4() { for( int i = 0; i < c_loadedList.GetItemCount(); i++) { if( c_loadedList.GetItemState( i, LVIS_FOCUSED) == LVIS_FOCUSED) c_loadedList.SetItemState( i, 0, LVIS_FOCUSED); // deselect else c_loadedList.SetItemState( i, LVIS_FOCUSED, LVIS_FOCUSED); // select } } but it doesn't seem to work. When I select something and click the "invert" b...

Transation sequence number issue
Happy new year everybody!! I have a customer with this situation. his invoice must be sequentially he can't skip a transaction number for his invoices, when a transaction is aborted a number is skiped. i konw that one is not hard to fix but this is the main issue when he do a return he need to have a differnt count numbers than the transactions number for example when he sale something the transation number 1545 is generated but is he return or do a store credit the transaction number is 145 without affectin the secuence os the sale transaction number. can be done? the other probl...

Extracting Pivotable Selections
In a field, if you elect to only include certain items, then when you select all of those items the table displays (Multiple Items) rather than (All). Is there a way to obtain a list of the items that are included (or excluded) from the Multiple Items group? I know how to see the list, but is there a way to move that info to cells in the spreadsheet? The following code will print the page field items that aren't hidden: '============================ Sub ListFieldsVisible() 'lists visible page field items on a new worksheet Dim ws As Worksheet Dim pt As PivotTable Dim pf A...

Macro for detect palindromes and repeats in letters/numbers string
Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could b...

The number 1 is appened to filename after file is opened
I am trying to find out why the number 1 is added to the file name after it is opened in Excel? I am using Excel from Office 97. It only happens when I open the file from Windows Explorer or from a shortcut on my desktop. It sounds like windows is using New as the default action when you doubleclick on the filename in windows explorer. If you right click on a *.xls file, you'll see a bunch of options--one will be Open and one will be New. Open should be the bold (default) one. I bet you'll see New in bold. If that's the case, you can try this: Close Excel and Windows Start...

Re: Selective Averaging
Frank, Sorry I inadvertently deleted the duplicate A. It should read =AVERAGE(OFFSET(A1,COUNTA(A:A)-5,0,5,1)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Phillips" <> wrote in message news:... > Hi Frank, > > This should do it > > =AVERAGE(OFFSET(A1,COUNT(A)-5,0,5,1)) > > -- > > HTH > > Bob Phillips > ... looking out across Poole Harbour to the Purbecks > (remove nothere from the email address if mail...

SendObject acSendNoObject Selecting Emails and Outlook
Hi, What i have is a list box that displays two fields from my table, it displays a name of a charity and their email address. This list box allows me to select multiple values. I have a command button with no function. What i want to happen is when i click on the button, it will take the email addresses of the charitys selected and populate the To: field in Microsoft Outlook. People have been referring me to websites and i am just not gettin the idea of things. Can someone please post the code for this? Table and form name = Charities List box name = List1 Field Name in list box = Name...

Lookups and References in relations to countA and sum products....
I have 2 sheets, one summary, and one detail. The detail is as follows: Dept units 331 12 331 24 331 331 12 332 332 36 332 24 333 The summary is as follows: Dept # of styles 331 3 332 2 333 0 I want the formula on the summary sheet to count the number of non blank entries for each dept. Which formula is it? -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: View this thread: http://ww...