need formula help please

i have two cell ranges, b34:b134 and i34:i134 and i need to know for each
'b' cells that contain a specific value, how many of the 'i' cells (in the
same row) contain a 'y'.

i tried =SUM(IF(B33:B134="1",IF(J33:J134="y",IF(J33:J134="n",1,0)))) but it
counts all the values in the 'b' row that batch to a '1' and all the values
in the 'i' row that match to a 'y'.  i need it to count all the '1's in the
'b' cells that have a 'y' in the 'i' cell.
        'b'            'i'
34     1             y
35     2             n
36     1             n
37     2             y
38     1             y

in cell j15, i want a value of 2  (the number of  1's in the 'b' column that
have a 'y' in the 'i' column.

your help is much appreicated.
john w zerkel


0
jjj1 (2)
1/12/2004 5:41:31 AM
excel 39879 articles. 2 followers. Follow

2 Replies
307 Views

Similar Articles

[PageSpeed] 36

Hi John!

Try one way:

=SUMPRODUCT((B33:B134=1)*(I33:I134="y"))

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/12/2004 7:01:39 AM
i discovered the error in my formula.  what will work is:

=SUM(IF((B34:B134=1)*(I34:I134="y"),1,0))


"john" <jjj@jjj.com> wrote in message
news:6YGdnbWeO9oQrp_d4p2dnA@giganews.com...
> i have two cell ranges, b34:b134 and i34:i134 and i need to know for each
> 'b' cells that contain a specific value, how many of the 'i' cells (in the
> same row) contain a 'y'.
>
> i tried =SUM(IF(B33:B134="1",IF(J33:J134="y",IF(J33:J134="n",1,0)))) but
it
> counts all the values in the 'b' row that batch to a '1' and all the
values
> in the 'i' row that match to a 'y'.  i need it to count all the '1's in
the
> 'b' cells that have a 'y' in the 'i' cell.
>         'b'            'i'
> 34     1             y
> 35     2             n
> 36     1             n
> 37     2             y
> 38     1             y
>
> in cell j15, i want a value of 2  (the number of  1's in the 'b' column
that
> have a 'y' in the 'i' column.
>
> your help is much appreicated.
> john w zerkel
>
>


0
jjj1 (2)
1/12/2004 2:49:40 PM
Reply:

Similar Artilces:

E-mail hyperlinks-need to disable these #2
In Excel 2000 (I know, should update the program, but since I am low-level in a large government agency, this is not an option), is it possible to turn off the automatic creation of a hyperlink when entering an e-mail address? I'd like these to show as plain text, but I can't find any solution other than individually removing the hyperlink, but this is a pain when many addresses are created. I saw a solution for Excel 2002, but this doesn't appear in 2000 (Tools / AutoCorrect Options.... "Autoformat as you type" tab, uncheck "internet and network paths ...

=if formulae
I have and excel spreadsheet which I wish to record an error message if one cell as a value of more than zero and the cell below it has a value of zero. I have managed the first part of the formula but can not figure out how if the both cells are zero to return a " " result. "Janie" wrote: > I have and excel spreadsheet which I wish to > record an error message if one cell as a value > of more than zero and the cell below it has a > value of zero. Ostensibly: =if(and(A1>0,A2=0), "ERROR", "") ----- original message...

What do I need to change to add a contact while offline?
Gurus, Running Outlook 2003 in Corporate mode. Exchange 2003 server on the back-end. I have the following questions. 1. What do I need to change to add a contact while offline? When I try to add one Outlook says "Outlook must be online to complete this action." 2. I can no longer address emails by clicking on TO to draw on Global addressbook or contacts. I can reply on to messages already in my Inbox because the message address is already there. -- Spin 1) Add the contact to the Contacts folder in your mailbox, not in the = Address Book.=20 2) Why not? In other words, wh...

VBA variable definition help needed.
Hi all! I have run across an issue with data retrieval. At the company I work for, the year runs from November 1, 2008-October 31, 2009. In one of our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two months (nov and dec) the year will be 2008. How can I change the mYear variable below to accomodate the year 2008 when it actually resides in the 2009 folder? My apologies, I'm very new to excel and VBA. Thanks. sdate = Range("B2").Value mYear = Format(sdate, "...

I need a derived ClistCtrl...
All, I use CListCtrls in my app extensively. I now need a control whereby I can have one row which effectively has a number of child rows. Ideally what I want is a [+] sign on one of the rows ala-tree control which when clicked inserts a number of child rows below it with a kind of parent-child relationship. The scenario I have is that each server has many processes, I want to show both the server and all the processes in this CListCtrl but still have a notion of some relationship. Does anyone know of such a control? PT This may help http://www.codeproject.com/treectrl/ctreelist...

Status unreachable PLEASE HELP
Hello all, When I go to tools => Monitoring and status => status in the exchange system manager, in the right pane, one of my servers in another routing group is reported unreachable. However, mail connectivity is working between the 2 routing groups. Any idea's? Thanks Glenn ...

Formula selection: a real tough one
Folks, This newsgroup has been extremely helpful over the years and I am thankful to all whom have helped me in the past. I now have a scenario that I can not even begin to solve and hope that someone can send me in the right direction. Our ERP system can export transactional data. I would like to be able to analyze the data on an invoice by invoice basis to determine metrics such as average total invoice amount for invoices that include a specific part number. The ERP data extract looks something like this: CustNo Date InvNumber ItemNo QTY UnitPr ExtPr UnitCo Ext...

Excel Macro Help.....
I have this to insert Data from word to the first empty row in excel bu somehow it replaces the first row and that's where it only insert data... Any Help?:rolleyes -- Message posted from http://www.ExcelForum.com Do you want to get the first empty row? For column A, that can be got with Cells(Rows.Count,"A").End(xlUp).Row+1 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alihussain19 >" <<alihussain19.14rm4o@excelforum-nospam.com> wrote in message news:alihu...

Need help with SELECT statement. 04-19-10
Hi, I need some help with the select statement given the business rules and desired result below. Thank you so much for your help in advance. Please see the business rules below. IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL DROP TABLE #Office CREATE TABLE #Office ( CompanyId INT NULL, OfficeId INT NULL, CorpOffice BIT NULL, DBAName VARCHAR(15) NULL ) go INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1') INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2') INSERT INTO #Office VALUES (1323, 1306, 0, 'Te...

I need open or gain access to an external file
Here is my problem and I hope someone can help me out. I am trying to open or retrieve data from a file that is only accessable from a different user. Example: If you are logged in under user and need to access a file that is only available under admin. The files are unavailable under user because they do not need to be tampered with. These files cannot be made public. Is there a way to do this? Doesn't that sound like it would defeat the purpose of having the file placed where you can't/shouldn't be able to get to it? -- Best Regards, Luke M "P.Griesh...

need two email accounts for two phones on one computer
My wife and I have just bought an iphone each after a recent burglary. We are trying to maintain separate contacts. Apple advice was to set up a new user for my wife to stop us synching with each others contacts etc. I've et my wife up as a new user but don't know how to set up outlook with separate outlook account so that we can get our own emails and maintain our own contacts. I did manage to set up a replica outlook email account but it generally means we get the same emails in each account. Is it possible for us both to have a separate outlook installation under ...

AutoFormat
OK We often export mailing data from our database into a CSV file which we then open in Excel to send to our bulk mailing contractor. A problem has recently come to light however. US Zip codes starting with a zero - Excel automatically chops the leading 0 off - leading to returned magazines for us. Is there any way to stop Excel automatically guessing the format of the cell and chopping the leading zero off? I don't want to have to change our data by adding a - or something to kick the cell into text format - Just want to stop excel ruining our data automatically. I prefer to do that m...

Help needed consolidating lines
Hi all. I have an issue where i have several lines on a spreadsheet where i want to consolidate it onto one line. Sounds simple i know, please see the attached file as it explains my situation far more clearly. (example.jpg). Many thanks Darren +-------------------------------------------------------------------+ |Filename: example.JPG | |Download: http://www.excelforum.com/attachment.php?postid=4199 | +-------------------------------------------------------------------+ -- plusoneservices -------------------------------------------------...

Help with front page drop down box
Hi, i need to know how to direct people to the drop down option they select from a website Try this: http://www.echoecho.com/tooldropdown.htm -- ~ Kathleen Anderson Microsoft MVP - Expression Web Spider Web Woman Designs Expression Web Resources: http://www.spiderwebwoman.com/xweb/ Expression Web Wiki: http://expression-web-wiki.com/ FrontPage Resources: http://www.spiderwebwoman.com/resources/ Please reply to the newsgroup for the benefit of others "Michael" <icouldbeelvis@discussions.microsoft.com> wrote in message news:8309C283-377B-4A6B-A7FD-3A6508...

help
I'm using Excel 2003. I've got two worksheets of data in my workbook, one copied from a text file, the other downloaded from the Bureau of the Census, originally in .csv format and saved as an .xls file. I'm using vlookup to use the state name in one sheet to lookup the data value I want from the other worksheet. Vlookup returns the value of the last cell in the table rather than the value that should be returned. I've done some simple testing on the lookup value relative to that in the table array. Both entries are the same length. Both are text (istext). Mat...

I need HELP, formula for interest accrued daily. #2
I don't see how this will take into consideration the principle amounts Something is missing or I am still confused. Please advise further. Thanks. Marshall Lewi -- ml@po ----------------------------------------------------------------------- ml@pok's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1622 View this thread: http://www.excelforum.com/showthread.php?threadid=27637 Marshal, Multiply the principal by the result of that formula to get the amount of interest. "ml@pok" <mlpok.1ffo1z@excelforum-nospam.com> wrote in message news:mlpo...

Counting Text Occurances
I have numerous (approx. 30) 2 letter codes used to identify different type of work events. Some are "soft" events, some are "hard" events. I want to count how many times each type of event occurs in a range. Soft = DO, VC, SK, JD, CP, etc. Hard = FT, TH, WK, MT, etc. My data is entered across columns by date and down rows by employee. Does anyone know how to sum the "soft" and "hard" events by row / employee? Thanks. Hi, Assume that your first employee data is in row 21 (Let's say the workevents are in D21:I21). In C26:C3...

help with summing fields in repot
I have a field called ponts assigned in a report. It uses an if statement to assign points based on which class my students are attending. I want to sum the total points accrued for each student. In the detail section I made sure I named the if statement field pointsassigned and then I entered a text box with =sum([pointsassigned]) when I run the report, It doesn't recognize pointsassigned. I checked spelling etc. I even tried =abs(sum([pointsassigned])) it doesn't recognize the name I assigned. Any thoughts on what I am doing wrong ? I appreciate any help Thank...

owa advice needed
we are running Exchange 2003 SP1 in a single domain W2k native environment... we're currently considering using RSA secure VPN access to let users outside our network access their email (each user would connect using \\servername\exchange). in order to get this to work properly however, our VPN support guys are saying we have to change all the permissions on our Exchweb folder and subfolders to 'anonymous' (from Integrated and Basic). are we at risk internally of someone sniffing our network with this arrangement? I know the MS recommendation is using a front end server in the ...

Beginner
Hi all, I have outlook 2002 and have set myself up with an e-mail address but can't seem to get it to work properly. I need help! When I send any mail to any (even my work address)e-mail address, the following message comes up The following recipient(s) could not be reached: ..........@........com' on 18/02/2004 19:02 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) Even when I send a mail to my home e-mail address as a test, it defaults to a web based address. HELP PLEASE.... look up this webpage. http://mla.libertine.org/tmda-...

Formula for weeks
I was wondering if there is a formula to calculate how many weeks it is from today to a certain date This formula will calculate how many days =DATEDIF(Date1,Date2,"d") or =DATEDIF(A1, B1,"d") with dates in A1 and B1 If by week you mean any seven days (not a Sun to Sat) then =DATEDIF(A1, B1,"d") /7 best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Shihachi" <Shihachi@discussions.microsoft.com> wrote in message news:EEBBAFED-0786-40C5-9E1F-57901EFD456F@microsoft.com... > I was wondering i...

formula needed #4
Thanks for the help. I like that way a lot better. I actually got a formula to work, and it probably follows along with the suggested earlier. It is written with the names of the tabs I had to use, but maybe someone else will be able to use it. Again, thanks to everyone. =SUMPRODUCT(--('Call Frequency'!$A$1:$A$50000<>"")/COUNTIF('Call Frequency'!$A$1:$A$50000,'Call Frequency'!$A$1:$A$50000&""),--('Call Frequency'!$C$1:$C$50000='Repeat Model Type Breakdown'!A2)) Gary's Student Wrote: > You can accomplish your coun...

VBA Programming help
Hello, I have an Excel file with 2 worksheets. One containing Order Info called "Orders" and another named "Analysis" which contact some fixed data and two columns for automatic fill in from the "Orders" sheet. I would like to create a code or macro when execute, transfer the Order# and Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty into account. so, if the order# has a quantity of 2, then it will fill in two rows for one Order. The sample data is below. Thanks for any help. Analysis sheet: Line...

Formula vs Constant #2
Is there a formula that will check a cell to determine if it is a constant (123) vs a formula (=a1)? Only if you use a UDF http://www.mvps.org/dmcritchie/excel/formula.htm#HasFormula -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Jonathan Cooper" <JonathanCooper@discussions.microsoft.com> wrote in message news:2ED0251B-E26A-46C3-803C-9B8FA51EEE5D@microsoft.com... > Is there a formula that will check a cell to determine if it is a constant (123) vs a formula (=a1)? Jonathan User Defined ...

Help in Excel #4
When i type then format an excel sheet it changes when i go to preview it. Some cells have two lines but in the preview and print it all goes onto one and leaves big spaces. Is there an option to print and view as typed. I thought there was a format option but I have tried everything and nothing.... Please anybody help ...