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
757 Views

Similar Articles

[PageSpeed] 53

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:

Deleting filtered results
What am I doing wrong in Excel 2007? In prior versions, I would filter my results, then highlighting the row numbers, I would delete those results I didn't want. Then when I turned off the filter, I would have only the items I wanted. In Excel 2007, when I do this, all items are deleted, even the one's that were hidden by the filter. Any ideas? Thanks, Dennis Maybe... Deleting or modifying rows that are above and below a hidden row in a filtered Excel 2007 worksheet also deletes or modifies the hidden row http://support.microsoft.com/kb/928974/en-us But if you want to be ca...

Is there a more in-depth tutorial on conditional formatting in cha
I've seen the site: http://peltiertech.com/Excel/Charts/ConditionalChart1.html but I don't quite "get it" . Is there a site that explains it ... more... slowly, lol Thanks :) Hi, What is it you don't get? What type of chart are you trying to create and what conditions did you want to apply? Basically you use a separate series for each 1 of the conditions you have. The data in each one of the series is either present or not depending on the result of a formula which perform the conditional test. Cheers Andy Meenie wrote: > I've seen the site: http://peltie...

One more SQL question
I am trying to write a SQL statement that will pull the tracking number from the shipping table based on the accountnumber in the customer table. I am having trouble getting the JOIN statement correct. Can anyone help? Thanks, Nick ...

put query result to cbo control on the form
I have a query as follows: SELECT TOP 1 qryVisitData.timeIn FROM qryVisitData ORDER BY qryVisitData.timeIn DESC; I want to assign the query result to my cboTO control on my form so when I open the form, I already have the default date. How to do it? Thanks. Type that SQL statement in the Row Source property of the combobox. Do not use quotes to delimit what you input, in design view of the form, the property RowSource knows it is a string you are supplying. Also, be sure the Row Source Type is set to Table/Query (which is so, by default). Hoping it may help, Vanderghast, Access MVP...

Repeating column headings on printed pages
(Office 2k3) How do I repeat column headings on printed pages in addition to page 1? Choose File/Page Setup/Sheet. In the Print Titles section, enter your row number in the Rows to print at top textbox, e.g.: $1:$1 In article <#x9SHIMeEHA.3684@TK2MSFTNGP09.phx.gbl>, "Sydney Lotterby" <sydney@infosearch.com> wrote: > How do I repeat column headings on printed pages in addition to page 1? Thanks. "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-70DFCF.13391102082004@msnews.microsoft.com... > Choose File/Page Setup/S...

XslTransform not excluding default namespace despite exclude-result-prefixes attribute?
I'm writing an xslt in vs.net 2003 and in order to get intellisense on the html content I added the default namespace declaration xmlns="http://schemas.microsoft.com/intellisense/ie5". However, even though I also have exclude-result-prefixes="#default" declared the default namespace is still outputted on the resulting document. <?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://schemas.microsoft.com/intellisense/ie5" xmlns:...

finding 1 formula result in a group of cells
I have ten cells in a row that have a formula in each. Only one of the cells will display a result at any one time but it changes depending on the date. I need a formula that will find the cell displaying the result and display it in a single cell in another worksheet Assume the 10 cells are in A1:J1 in Sheet1 In another sheet, you could use this, normal ENTER: =INDEX(Sheet1!A1:J1,MATCH(TRUE,INDEX(TRIM(Sheet1!A1:J1)<>"",),0)) Success? Savour it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdeme...

Dynamically decide column in sumif formula
Hi I am going to demo my question with a simple example I have the following data: StockNo, Date, Sales, Boxes A xx 100 10 B xx 150 9 A xx 200 5 A xx 50 5 On another worksheet I want to find the sum of A sales. I type "A" into cell D6 as my criteria, and I have the following sumif formula: =SUMIF(Sheet2!A2:A46,D6,Sheet2!C2:C46) What this is doing is summing all the "C" column figures (sales) based on the if, so it sums the sales for stock "A". What I want to do i...

Only protecting the header cells and allowing new rows and columns to be created
i am developing a spreadsheet which i will be making available for download on my site im going to brand the spreadsheet with my logo etc I want to protec the logo and formulas etc I know how to do this However, when you protect the sheet - no new rows or new columns can be added apart from my branding and formulas etc i want visitors to be able to change as much as they would like how would i do this? --- Message posted from http://www.ExcelForum.com/ Hi depending on your excel version you can allo ro isnertions in the protection dialog (just check this option) -- Regards Frank Kab...

sort whole sheet by one column?
Can I make all the info in each row shift together when I sort by a particular column -Courtney Yes - if it doesn't automatically do this, it's probably because there are breaks in your data which make Excel think there are separate ranges. Highlight all the columns you want to sort together, and put the current selection in the column you want to use to sort (use tab so the current cell is in the column), then sort. Another method is to select all the columns, then choose Data | Sort..., and you can then pick up to 3 columns to sort by, in decending order. (In fact if you want to s...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

Account Name blank on one account only
I'm using WLM for five different e-mail accounts. Four are POP and one is IMAP. It all works fine with one small exception. The main mail page is generally left on 'Quick View' so that all incoming mail shows in chronological order. One of the 'Columns' across the top is set to show the 'Account' so that I can identify whether the mail is personal, business, etc. by the account name. All the account names show in this column just as they are listed per: Tools/Accounts/Properties/General/Mail Account except for one, My @msn.com (Hotmail) account....

Have different row heights between columns
What do you have to do to have different row heights at different places in a spread sheet. Can you have adjacent columns with different row heights? AFAIK, it cant be done -- Message posted from http://www.ExcelForum.com Hi not possible -- Regards Frank Kabel Frankfurt, Germany reinhold wrote: > What do you have to do to have different row heights at different > places in a spread sheet. Can you have adjacent columns with > different row heights? You could merge cells to give the appearance of different rowheights. Merge A1:A3 and it'll look like A1 is the same height...

Database Diagram 15 ruled column limit?
I am creating a database model, before we build the database. This means that I cannot have Visio build this for me. I am using column shapes with connectors and just setting everything up. It has been going fine, but I have 1 issue. I searched for table and then for column. A shape called 15 ruled column is what I found. This works fine unless I have a table with more than 15 columns. Then it doesn't frame my field list after that. Any suggestions out there? I am frustrate that I can't find another shape, change this one to work or something. I have so much of this done, but ...

double your money in one year [albertans only]
i need 6 people to invest 200k to open a new shop land and buildings will be owned by 7 partners,every one gets the 200k back and you have a 12% interest in the company or the company will buy you out for 200k.double your money or stay on and recieve monthly payments.you can sell your 12%at any time for 200k.i live in alberta and i will have 12%also.contact poorboy2telus.net. ...

How to easily collapse columns?
Hi, Is there a way to hide and unhide columns easily without using the menus. I have a large spreadsheet and I would like to navigate it more easily. Any help appreciated Thanks G you might adapt this to your needs. You need to create a button and name it "toggleit" Sub HideG_UnhideG() If Columns("c:j").EntireColumn.Hidden = True Then Columns("c:j").EntireColumn.Hidden = False ActiveSheet.Shapes("toggleit").TextFrame.Characters.Text = _ "HIDE" Else Columns("c:j").EntireColumn.Hidden = True ActiveSheet.Sh...

Open form based on combo box value w/error message for blank combo box
I am working in an Access 2000 database that I've inherited. I'm trying to clean up some of the non-working functions. I am trying to open a form using the OnClick property for a button. On a job order form, there is an unbound combo box [CmbContactID] that gets its list from querying Table!ContactInformation.[CustomerCodes]. When populated, [CmbContactID] should provide the value to open FrmContactInformation when [CmdOpenContactForm] (a button) is clicked. I want an error message to display if the field is blank otherwise it will open the form for the customer code that is displaye...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

Sorting in one tabs messes up formulas in another tab
Hi, I'm not sure if this problem was solved before so sorry if this is a repeat question. Let's say I have one spreadsheet tab that originally has the following: Col A Col B Row 1: 3 3 Row 2: 5 5 In another tab, I have the following formulas that reference the 1st tab: Col A Row 1: =average(A1:B1) - which would yield the number 3 Row 2: = average (A2:B2) - which would yield the number 5 Now let's say I resort all the data in the first tab, by col A, in descending order. The first tab now looks like: Col A Col B Row 1: 5 5 Row 2: 3 3 All is well enough until you look at the v...

Hyperlinks from one document to another document on a different dr
When I have made a Hyperlink from a document in one drive to a document in another drive it works until I save it go out of the file. When I log back into the file that had the link set in it it does not work, gives an error message when the link is selected: The address of this site is valid, check the address and try again. I have re-done this quite a few times with the same results. Help ...

MsQuery returning one record + one empty record
Hi all, In the same excel file, i have one sheet containing data and one running a MSQuery to retrive some of the data with a parameter. The problem is that when only one record should be returned, I have that record and a blank record. Because I have formulas that are auto-extended, I have two lines of formula. In MsQuery UI, there's only one record. I hope this is clear, Thanks Pierre-André ...

Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The spreadsheets currently have 100 columns (from 1-100). From there i have to reduce the number of columns set to 8 columns. E.g: 1 2 3 4 5 6 7 8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it goes to the 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 How do i do that without altering the order of the value of the cell? Let me make sure we understand the question. You have ...

Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending...

One use unabel to access Outlook webmail
I have a costumer that are running small biss 2003 with exchange. all are abole to read there mail throw outlook webmail except for one. we have tried changing her password, she is inn all the same groups as the others and she has outlook web enaboled as the other users. Any suggestion? What happens when she attempts to logon? Are there corresponding entries in the event logs? Nue "hans jacobsen" <hansjacobsen@discussions.microsoft.com> wrote in message news:73BF4D97-67B6-4C45-9BE6-6D3DFB2AE7B6@microsoft.com... >I have a costumer that are running small biss 2003 with e...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...