Help needed urgently

Hi,

Im trying to check that one row of data satifies a criteria, then if it does 
it counts an entry in another column adjescent to it.

Example: -

Column A - Column B
Blay - Y
Blay - N
Blay - Y
Chop - N
Chop - N
High - Y

What i would be asking (of a much bigger table) is If Column A = Blaydon and 
then column B (same row) = Y, then count. In this example I would want 2 
returned as the answer.
Is this possible?
Thanks
-- 
Andy
0
Utf
5/29/2010 2:01:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1000 Views

Similar Articles

[PageSpeed] 28

Try one of these...

Use cells to hold the criteria:

D2 = Blaydon
E2 = Y

If you're using Excel 2007 or later:

=COUNTIFS(A2:A7,D2,B2:B7,E2)

This will work in any (modern) version of Excel:

=SUMPRODUCT(--(A2:A7=D2),--(B2:B7=E2))

-- 
Biff
Microsoft Excel MVP


"AndyW" <AndyW@discussions.microsoft.com> wrote in message 
news:A5AA122F-9A81-4909-8E58-787ADA01714E@microsoft.com...
> Hi,
>
> Im trying to check that one row of data satifies a criteria, then if it 
> does
> it counts an entry in another column adjescent to it.
>
> Example: -
>
> Column A - Column B
> Blay - Y
> Blay - N
> Blay - Y
> Chop - N
> Chop - N
> High - Y
>
> What i would be asking (of a much bigger table) is If Column A = Blaydon 
> and
> then column B (same row) = Y, then count. In this example I would want 2
> returned as the answer.
> Is this possible?
> Thanks
> -- 
> Andy 


0
T
5/29/2010 2:09:10 PM
AndyW;734272 Wrote: 
> 
Hi,
> 
> Im trying to check that one row of data satifies a criteria, then if it
does
> it counts an entry in another column adjescent to it.
> 
> Example: -
> 
> Column A - Column B
> Blay - Y
> Blay - N
> Blay - Y
> Chop - N
> Chop - N
> High - Y
> 
> What i would be asking (of a much bigger table) is If Column A =
Blaydon and
> then column B (same row) = Y, then count. In this example I would want
2
> returned as the answer.
> Is this possible?
> Thanks
> --
> Andy




Hi

this should do what you want

=SUMPRODUCT((A2:A7="Blay")*(B2:B7="Y"))


mrH


-- 
mrhichens
------------------------------------------------------------------------
mrhichens's Profile: http://www.thecodecage.com/forumz/member.php?u=1221
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=206043

http://www.thecodecage.com/forumz

0
mrhichens
5/29/2010 2:14:18 PM
Try this (based on your example):

=SUMPRODUCT((A1:A6="Blay")*(B1:B6="Y"))

Regards,

Tom


"AndyW" wrote:

> Hi,
> 
> Im trying to check that one row of data satifies a criteria, then if it does 
> it counts an entry in another column adjescent to it.
> 
> Example: -
> 
> Column A - Column B
> Blay - Y
> Blay - N
> Blay - Y
> Chop - N
> Chop - N
> High - Y
> 
> What i would be asking (of a much bigger table) is If Column A = Blaydon and 
> then column B (same row) = Y, then count. In this example I would want 2 
> returned as the answer.
> Is this possible?
> Thanks
> -- 
> Andy
0
Utf
5/29/2010 2:44:01 PM
Thanks everyone this works great
-- 
Andy


"T. Valko" wrote:

> Try one of these...
> 
> Use cells to hold the criteria:
> 
> D2 = Blaydon
> E2 = Y
> 
> If you're using Excel 2007 or later:
> 
> =COUNTIFS(A2:A7,D2,B2:B7,E2)
> 
> This will work in any (modern) version of Excel:
> 
> =SUMPRODUCT(--(A2:A7=D2),--(B2:B7=E2))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "AndyW" <AndyW@discussions.microsoft.com> wrote in message 
> news:A5AA122F-9A81-4909-8E58-787ADA01714E@microsoft.com...
> > Hi,
> >
> > Im trying to check that one row of data satifies a criteria, then if it 
> > does
> > it counts an entry in another column adjescent to it.
> >
> > Example: -
> >
> > Column A - Column B
> > Blay - Y
> > Blay - N
> > Blay - Y
> > Chop - N
> > Chop - N
> > High - Y
> >
> > What i would be asking (of a much bigger table) is If Column A = Blaydon 
> > and
> > then column B (same row) = Y, then count. In this example I would want 2
> > returned as the answer.
> > Is this possible?
> > Thanks
> > -- 
> > Andy 
> 
> 
> .
> 
0
Utf
5/30/2010 8:30:01 AM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"AndyW" <AndyW@discussions.microsoft.com> wrote in message 
news:EEC73090-2DD7-4666-9825-025971E678C4@microsoft.com...
> Thanks everyone this works great
> -- 
> Andy
>
>
> "T. Valko" wrote:
>
>> Try one of these...
>>
>> Use cells to hold the criteria:
>>
>> D2 = Blaydon
>> E2 = Y
>>
>> If you're using Excel 2007 or later:
>>
>> =COUNTIFS(A2:A7,D2,B2:B7,E2)
>>
>> This will work in any (modern) version of Excel:
>>
>> =SUMPRODUCT(--(A2:A7=D2),--(B2:B7=E2))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "AndyW" <AndyW@discussions.microsoft.com> wrote in message
>> news:A5AA122F-9A81-4909-8E58-787ADA01714E@microsoft.com...
>> > Hi,
>> >
>> > Im trying to check that one row of data satifies a criteria, then if it
>> > does
>> > it counts an entry in another column adjescent to it.
>> >
>> > Example: -
>> >
>> > Column A - Column B
>> > Blay - Y
>> > Blay - N
>> > Blay - Y
>> > Chop - N
>> > Chop - N
>> > High - Y
>> >
>> > What i would be asking (of a much bigger table) is If Column A = 
>> > Blaydon
>> > and
>> > then column B (same row) = Y, then count. In this example I would want 
>> > 2
>> > returned as the answer.
>> > Is this possible?
>> > Thanks
>> > -- 
>> > Andy
>>
>>
>> .
>> 


0
T
5/30/2010 4:07:20 PM
Reply:

Similar Artilces:

need to reload 2007 home/student have prod key
where do i go to reload 2007 home/student i have product key On 2/04/2010 7:21 PM, howie2006 wrote: > where do i go to reload 2007 home/student i have product key Where is the CD? On 2/04/2010 7:21 PM, howie2006 wrote: > where do i go to reload 2007 home/student i have product key Sorry that was not very helpful. If you had the CD you would not be posting here. The only thing I could find was a 60 day Office professional trial. http://trial.trymicrosoftoffice.com/ HTH http://www.microsoft.com/office/downloads/ ...

Help Sendobjest
I am hopping some one can help me I have an application in Access 2000 which has been running on XP and XP professional for a very long time every morning this application creates a list of reminders it then creates an E-Mail for each reminder and with a loop it sends all the e-mails one at a time the code is DoCmd.SendObject acSendNoObject, , acFormatTXT, StrFullAddress, , , strMailSubject, strMailMsg, False All of a sudden a couple of months ago I noticed that it was sending the first mail ok with the usual out look message and then it just looped through the rest but did not send...

meeting invites now show up as emails only
As of very recently, Outlook invites sent from my work calendar to my home calendar (both Outlook, although work Outlook is 2003 I think) now only show up as emails, not as calendar invites. Does anyone have an idea why this is happening - it used to work just fine. Fixes? ...

Please Help: Toolbars in DLL Dialog
Hello, Creating toolbars in an EXE app is a relative piece of cake. However, I am trying to insert a toolbar into a DLL dialog. This is how my DLL dialog works: 1) Contents of a function exporting a dialog AFX_MANAGE_STATE(AfxGetStaticModuleState()); CSvg* dlg; try { dlg = new CSvg(); dlg->Create(IDD_MAIN_FRAME); dlg->ShowWindow(SW_SHOW); } catch (CSvg *dlg) { dlg->PostNcDestroy(); delete dlg; } 2) Use a function in my Dialog class to insert a toolbar: int CSvg::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CDialog::OnCreate(lpCreateStruct) == -1) return -...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

I have over 100 excel sheets i need put in alphabetic order,how?
Background: I have an index page with hyperlinks to each sheet, each sheet has a COUNT function to add the entries and a SUM function on the index page which adds the result of each COUNT function, to do that I used "SUM(Birmingham:Worcester'!B2)" as each sheet has a geographical name a newly added sheet may not fall within the specified range and therefore may not be added. Is there a way to alphabetise these sheets? Rob, have a look here, http://www.cpearson.com/excel/sortws.htm -- Paul B Always backup your data before trying something new Please post any response to t...

need to allow negative quantitys on purchase orders
because our vendors list returns and exchanges on invoice i need the abillity to enter negative quantitys when recieving manually. any ideas? ---------------- 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...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

NDR Help
Hi, I hope someone can help on this. A few people have complained they cannot e-mail to us always. They get an NDR. This is very intermittent it seems and not everyone is affected. They have forwarded the NDR they have received. I've attached it below. I can't work out if our mail server has sent that to them or if it is generated by their company e-mail server. This happens mainly with one company but i think it has happened to some others as well - i can't be sure but the one company mainly (or even only). It is odd in that they can e-mail and get through then do it aga...

Drag and drop help
I am trying to get drag and drop working between a few windows in my application. I can get the dragging started, but can't get away from the 'no-entry' mouse cursor. I have read through the MSDN and I have done the following in a CTreeView derived class called TreeCtrlArtifacts: void TreeCtrlArtifacts::OnTvnBegindrag(NMHDR *pNMHDR, LRESULT *pResult) { LPNMTREEVIEW pNMTreeView = reinterpret_cast<LPNMTREEVIEW>(pNMHDR); // TODO: Add your control notification handler code here CUIntArray arrayOfSelected; arrayOfSelected.Add(0); CMainFrame *main = (CMainFrame*) AfxGetMa...

Recordset Help Needed
I have the below code that works some of the times, but not all. Here is my issue. If there is no records in the table it works the first time. Then when I go to add another record it says "No Current Record". If I close the form and then re-open it, it work. I thought by adding the Set rs = Nothing it may help. It did seem to do anything. Private Sub TRAN_TYPE_DblClick(Cancel As Integer) Dim rs As DAO.Recordset Dim rs1 As DAO.Recordset 'Save any edits If Me.Dirty Then Me.Dirty = False End If 'Check there is an order in the form. If...

*CANT log into Windows -please help
Hi, I need step by step instructions on how to use my recovery console CD in order to fix a problem. Here is my prob: A virus/malware scan I did probably messed up my logon file. The antivrus program either removed or quaranteined the virus/trojan that was attached to my logon file. Because when AVG prompted me to reboot after this scan, It stopped at the welcome screen logon part. I click on my user name (only one there) and it says its logging on...But then it says saving settings and nothing else happens. My machine does not reboot or anything. It just stays at this logon sc...

need basic help setting up a graph
Hello, I am trying to set up a chart of days and times. The X- axis shoul have the days of the week (mon, tues, etc) and the Y - axis should hav various times of the day. I would like to have a line from on specific time on one day to another specific time on the next. So, point would be monday at 10:00 and the next would be tuesday at 1:00 followed by wednesday at 12:00, etc. Any suggestions?? Thanks fo your help Davi -- Message posted from http://www.ExcelForum.com Hi David, See my attachment. Is that the situation you explained? I use formula =IF(B2<0.5,B2+1,B2) in column ...

Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails Hi all. I'm new to this site and would appreciate some formatting help. I created a new Word doc and copied and pasted some graphics and some simple text boxes in to it. The graphics and text boxes were created in Powerpoint by someone else. Looks fine on my screen. Before sending it out via e-mail to an Outlook e-mail group I have, I first sent myself a test message to see what it would look like on the receiving end. It looks a mess! The text boxes are all over the place, as is the text that is not in a box. Also, everything slide over to ...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

HELP! CFtpConnection GetFile cause memory leaking, WHY?
I'm trying to use CFtpConnection GetFile to download files from a WS_FTP server. I found if I download 1000 files (Size from 17KB to 25KB ), it can cause about 10M memory leaking. When I commented out the GetFile( ) line, there was no memorry leaking. WHY? Following is part of my source code: try { // Request a connection to Image Server //Use default FTP Port //Use Passive MODE pConnect =3D sess.GetFtpConnection(strISIP,strFtpUser,...

Need PO Generator Replenishment Level Help
I=92m confused as to exactly how the Replenishment Level works. On the Purchase Order Generator Item Maintenance screen you can select a Replenishment Level. There are 3 choices: Order Point Qty, Order Up- To Level and Vendor EOQ (if Order Method is set to Independent Site). Online help states: Order Point Qty Enter the quantity this item should be reordered at to maintain your desired stock, including a safety stock quantity. This quantity is used to calculate the quantity to reorder when you print the Purchase Advice Report. For example, if the reorder level is 10, and there are eight in...

Crystal Reports help
We are currently using GP 8.0 and Crystal Reports 10. We will be running most of our reports through Crystal because you can do so much more than with the Report Writer. I am looking for a good reference book for Crystal 10 (this is the first version I have ever used) any suggestions? You can get books on Crystal 10 in any large bookseller. One thing you won't get is how to use Crystal with Great Plains. There was some training materials available. You will want to consult the Great Plains SDK for help on the tables to use. JO wrote: > We are currently using GP 8.0 and Crystal R...

Help with an IIF expression
My database is similar to a checkbook register and contains the following fields: Amount Combo box with the expense categories of Housing, Medical, Personal I have a report that gives me a monthly total for each of these categories and a grand total of all categories for the month. However, I need help in calculating a monthly total for Medical and Personal combined. Please help me with the expression. I want to add a text box to my report to show the results of this expression. Thank you! You can create a hard-coded expression like: =Sum(Abs([ExpCat] IN ("Medi...

Help, how do I get an exe file with outlook 2002.
is there a way to turn off the GD stupid F#@$ing level one interecept "feature" in outlook? I am so angry with this stupid program@! I need to download an exe file and cannot with this software! Maybe if you washed your mouth out with soap, someone would be willing to help you. This is a technical forum -- not a place for ranting fools. "Sean" <sean@pointblankinc.com> wrote in message news:5a8001c42d8f$52c08b20$a401280a@phx.gbl... > is there a way to turn off the GD stupid F#@$ing level > one interecept "feature" in outlook? > > I am so angr...

HELP: Email body deleted when I receive voting response
Hi All, Whenever I vote using voting buttons, Email body gets deleted when the voting response is sent. So for eg: If I have enabled voting buttons and asked to vote on something from person xyz, the person xyz votes "accept" or "reject" and the email body is deleted when I receive the vote response email. Is there a solution for this? I want the email body to stay when I receive the voting responses. Thanks in advance. ...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

Restoring Exchange Data
HI. I experienced a crash with my Windows 2000 server which is the Global Catalog and dns server. Anyhow in my infinite wisdom I decided to do soemthing with the exchange server and in the meantime - it blew any active directory info which had replicated to the exchange server. The Exchange server was a part of the domain with AD installed, etc. The exch. server is a running windows 2003 server w/ exch. server 2003. Fast forward...I rebuilt the Windows 2000 server and its backup. I also rebuilt the Exchange Server...not completely knowing how to proceed....I installed w2k3 with all ...

Worksheet Help req PLZ
Hey All, I have a small issue where I have created a worksheet called data and in Cell A3 I have a linked cell to another worksheet and cell "WK48!A2" In Cell A4 in the "data" worksheet I need a link to "WK49!A2". I would like to link all cells in Column A to consecutively "WKxx" worksheet once I add them to the workbook. I thought I could drag them mouse down but al I get is links to consecutive cells in "WK48" work sheet. Please help mag()() Try =INDIRECT("'WK"&ROW(48:48)&"'!A2") -- Reg...

Help with SQL- SupplierList cost into Item cost field
Hi, re-posted! See below. Hi, thanks for that- it worked perfectly. There is just one snag however and I didn't think of it until I was testing. Many of the suppliers use specific currencies so the information I need copied from the Supplier tab is actually the Local Cost. When I view the SupplierList table, Local Cost doesn't appear as a column. Is it just a calculation based on exchange rates for information or does it actually exist somewhere within the database? Your help once again would be much appreciated. T. "convoluted" wrote: > Hi Tara - backup your...