Determine a result of one column based on conditions in two column

Example
Col A   Col B          Count the number of a's in Col B only when an x is in 
Col A
x          a           
x          a               Result should be 2
y          a
z          p               I can't figure it out 
x          t
x         m               Thanks
0
Utf
3/25/2010 4:30:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1085 Views

Similar Articles

[PageSpeed] 55

try this


=SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a"))
-- 
Hope this help

Please click the Yes button below if this post have helped answer your needs

Thank You

cheers, francis





"tel703" wrote:

> Example
> Col A   Col B          Count the number of a's in Col B only when an x is in 
> Col A
> x          a           
> x          a               Result should be 2
> y          a
> z          p               I can't figure it out 
> x          t
> x         m               Thanks
0
Utf
3/25/2010 5:01:01 PM
Reply:

Similar Artilces:

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Help with conditional formatting with 2000
Any help would be greatly appreciated. I am trying to group data together into increments of 10% of th numbers and then chart them based on these groups. For example, I hav 300 data points that vary from 20 to 500 in value. I want them t appear in a chart based on the number of values that fall in the lowes 10% of numbers (ie. 20-40) then the next 10% (ie. 40-60) etc. up to th top 10% of numbers, but I do not want to manually determine what thes ranges are. I want to see a distribution of how many numbers fal within each 10% of values. I am not sure if this makes sense, please let me know...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Determining length of trend before significant retrace
In range of data I need to find jthe largest numerical movement in one direction without (x) retrace. (x in this case being a specified whole number) Please help with a formula or direct me to where I could find this information. Using Excel 2007 For example: in range of numerical data, the largest directional integer movement was 300 before the data moved 25 in reverse. Example 2: The largest directional integer movement was 224 before the data moved 30 in reverse. Assuming you have a column of numerical integer data in column B, In C2, enter =ABS(B2-B1) to calculate the magnitude ...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Office 207 One Note
My One Note has been working fine for at least 2 years. Then a couple of days ago two pages of my most important information in my main workbook just disappeared. I'm not sure but none of the other pages look the same. Does anyone have any idea what might have happened? Have had no other computer problems recently. -- Rockymtnwoman -------- Original-Nachricht -------- > My One Note has been working fine for at least 2 years. Then a couple of days > ago two pages of my most important information in my main workbook just > disappeared. > I'm not sure b...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

Outlook 2007 error event id: 35
Per hour i receive about 12'000 of these errors in the event viewer on my vista laptop. And usualy my outlook at some point just goes into the "not responding" mode. Then I have to kill the process from the task manager to be able to reopen outlook just to see it freeze again after a couple of seconds. I tried the whole stop indexing service and reinstall index service procedure to no avail. Does anyone else have a solution to this very anoying problem? Fred putzhilfe@gmail.com <putzhilfe@gmail.com> wrote: > Per hour i receive about 12'000 of these errors in the...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...