COUNTIF using 2 ranges??

OK, here's the deal.  I have a worksheet with about 500 rows of data, o
each row has a unique ticket number, a type code (1-9) and a compan
name (about 20 different companies)  What I need to do is get th
results for each company and each code.  Therefor, the number o
tickets that belong to company XYZ that have a code of 3 would b
entered into one field.  Then 4, 5, 6 etc.

I have tried COUNTIF and array formulas but have has no success.  I
you have any ideas, please share.
Thank

--
Message posted from http://www.ExcelForum.com

0
5/3/2004 3:16:45 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
405 Views

Similar Articles

[PageSpeed] 40

Snickstx, a pivot table will give you what you are looking for. All you have
to do is set up a table with Company and Type Code as row headings, and
Count of Ticket Number in the data area. You can find information on setting
up a pivot table here:
http://www.peltiertech.com/Excel/Pivots/pivottables.htm and here:
http://www.contextures.com/xlPivot01.html, as well as in Excel help.
-- 
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


"snickstx >" <<snickstx.15orhv@excelforum-nospam.com> wrote in message
news:snickstx.15orhv@excelforum-nospam.com...
> OK, here's the deal.  I have a worksheet with about 500 rows of data, on
> each row has a unique ticket number, a type code (1-9) and a company
> name (about 20 different companies)  What I need to do is get the
> results for each company and each code.  Therefor, the number of
> tickets that belong to company XYZ that have a code of 3 would be
> entered into one field.  Then 4, 5, 6 etc.
>
> I have tried COUNTIF and array formulas but have has no success.  If
> you have any ideas, please share.
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
DDMara (266)
5/3/2004 4:03:48 PM
try using data>filter>autofilter

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"snickstx >" <<snickstx.15orhv@excelforum-nospam.com> wrote in message
news:snickstx.15orhv@excelforum-nospam.com...
> OK, here's the deal.  I have a worksheet with about 500 rows of data, on
> each row has a unique ticket number, a type code (1-9) and a company
> name (about 20 different companies)  What I need to do is get the
> results for each company and each code.  Therefor, the number of
> tickets that belong to company XYZ that have a code of 3 would be
> entered into one field.  Then 4, 5, 6 etc.
>
> I have tried COUNTIF and array formulas but have has no success.  If
> you have any ideas, please share.
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
5/3/2004 4:06:45 PM
Thanks for the great replies.  After some trial and error I ended u
using:

=SUMPRODUCT(('WORKSHEET'!L2:L1000="COMPAN
NAME")*('WORKSHEET'!I2:I1000=CODE NUMBER))

I had tried this previously but had inadvertantly left the sort i
place on the original worksheet.  This left text in the number fiel
and caused the #N/A error.  After spending some time in this forum 
was able to uncover my mistake.  Hope this helps anyone else having 
similar problem.
Thanks Again!!

--
Message posted from http://www.ExcelForum.com

0
5/3/2004 9:05:59 PM
Reply:

Similar Artilces:

CRM Queue #2
Hi, Anybody can helpl to know... Is it possible to alert if any email comes to the CRM queue Thanks, SAB Create workflow triggered on create event of email where direction = incoming and email address = the queue email address :-) Roger "SyedAbdulbasith" wrote: > Hi, > Anybody can helpl to know... > Is it possible to alert if any email comes to the CRM queue > > Thanks, > SAB ...

comments in a cell #2
HYCH, Just a question? is there any way of using some vba that will add coments to a cell and have the comment box appear as it does if i add my own comments manually, would like the comment hidden until the cell is selected and then have it show up! obviously i know this happens when you manually process comments, but i would like the comment box to appear and display values that are in another worksheet within the same workbook. hope that makes sense. Steve Steve wrote: > > HYCH, > > Just a question? > > is there any way of using some vba that will add coments t...

How do I join 2 fields to limit criteria?
I am trying to set a criteria with an OR clause in between 2 fields. Let's call the 2 fields Field1 and Field2. I'm trying to do this example: Only show the records that have this criteria: Field1 = A, and Field2 = B,C,D,E,F,G These 2 fields contain the same type of data, and we need to limit the criteria together between the 2 fields?? Any ideas on how to do this when using excel to build a query in Microsoft query? Christen You could use an advanced filter. Assuming field1 and field2 refer to columns A and B, type this into an open cell (say M22) outside the data table: ...

VS2005... #2
Stupid question maybe - does VS2005 have full mfc capabilities? If I'm comfortable with VC++ 6.0 will there be any changes I have to make in the way I write code (unmanaged that is)? Also, is it possible to use C++ .dll's for web applications? Or anything written in VC++ for that matter? ...

Could Excel be used as accounting software?
If so, any tutorials. (I don't want to learn or bky quick books) I jus want to keep tabs on business expenses and I would rather not do it o paper -- Freddy_Kruge ----------------------------------------------------------------------- Freddy_Kruger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2962 View this thread: http://www.excelforum.com/showthread.php?threadid=52382 Theoretically yes but practically no. There a lot of other softwares particularly databases that could do a lot than Excel. These include SAP,Oracle etc., Are you saying that excel wou...

Add item using HTML Status Bar
I want to add a bunch of pictures of items to the HTML status bar and have the item added to the transaction when the cashier clicks on the item. Is that possible? I have the customization guide, I can modify the status.htm file to display the pictures, but I just need the code to add to the HTML file to trigger the adding of the item (again, if possible). P.S. I don't want to use the custom POS buttons (I already know how to add items that way). I almost have it figured out. I added this code to the Status.htm file: function addit() { qsBridge.FireEvent("PerformAddItem...

Can not right click items in Outlook 2003 #2
Hello, Has anyone come across a problem in Outlook 2003 11.6359.6360 SP1 where you can not right click items and get the quick menu items? So for example, if I right click a mail message nothing comes up, versus the reply, mark as read, etc items. Thanks Much Lots of people are reporting it, but we don't have a fix yet. Are you using a script blocker? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://ww...

Another Countif??
I have a spreadsheet containing various fields which is used to monito the amount of telephone calls logged by members of my department. I've used the countif function to break this down to calls logged b each individual. The next column I would like to add is the amount o calls *completed* by each individual. The completed column onl contains a true or false value. So at the moment it would read:- Name Calls Completed JOHN 5 ??? Not sure if I've explained this well but what I'm aiming for is t count Johns calls in one column(done) and in the next co...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

Trouble with contacts backup using Windows Mobile Device Center.
(Also posted in old thread relating to the subject) Hi, I joined the forum specifically for this problem and this is my first post. I'm just going to spare you all the introductory bs and get down to my concerns. And I didn't bother reading past the first page of this thread even though it is apparently like four years old and a solution has probably already been found. Forgive me, but I'm really frustrated because I'm pretty good with computers and I've been at a standstill trying to fix this for about 5 hours now. I'm running Windows Vista Home Editi...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

problem with fonts using Word as editor #3
All new messages come up with a superscript font. I remove the check mark and go into default, but new msgs keep coming up in superscrip.What is one to do? Leo Leo Kerner <l.kerner@sympatico.ca> wrote ... > All new messages come up with a superscript font. I remove the check > mark and go into default, but new msgs keep coming up in superscrip.What > is one to do? Since you keep asking, I'll reply with my first reaction to all word-as-email-editor problems: don't use Word as your email editor. It's a simple and 100% effective solution to this and a whole host of ot...

2 charts on 1 worksheet
I have 2 data sources that I want to make a chart for. This is for a company survey I need a chart for Performance and 1 for Importance. There are 12 questions that I need to make charts for. I really want to keep a Performance chart and an Importance chart for each question on the same worksheet. Any suggestions? I must be missing something... Select the performance data and use the Chart Wizard to create a chart. Then, select the importance data and use the Chart Wizard to create a chart. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA ad...

Installation Problem for Outlook Sales for CRM 1.2
I installed the Outlook Sales for CRM 1.2, however I cannot see the details of accounts, orders and etc. When I debug, I found the problem is that there is only one file(dmUpdate.xml) in folder of "\Program Files\Microsoft CRM\Client\res\Web\_xslfiles". All the files such as 1_Update.xsl and so on were not copied. When I copied them from CRM Server, everything works fine. Is it a bug in the Outlook sales installation package ? Thanks Drew ...

Postmaster address #2
Hi Everybody! Situation: Exch2k3SP1 on Win2k3Sp1 configured to allow delivery status notifications. Now, when an external recipient sends an email to an internal recipient requesting a Delivery receipt, the DSN is being sent by “postmaster@internaldomain.com” and it is not a good idea. I want to change it to “postmaster@externaldomain.com” Now I have already tried some things but I am still unsuccessful: I have set a masquerade domain (externaldomain.com) under SMTP virtual server, I have removed postmaster@internaldomain.com from the user account it was bounded to and I have added “po...

COUNTIF Question #2
I need help with the COUNTIF function. What I'm trying to do is count range of cells if they are equal to the value of a specific cell Example: COUNTIF (A1:A20,�=>B1�), but it does not look at what�s in B it only counts it if the cell value is B1. Does anyone know how t accomplish this, I would greatly appreciate any help -- patrick s ----------------------------------------------------------------------- patrick s.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1469 View this thread: http://www.excelforum.com/showthread.php?threadid=26318 Hi siehe Ex...

Purchase Requisition #2
Good day! Please assist. For the purchase requsition module in MGP, I am unable to approve the request items. This has left the open request item always outstanding for the requester even though the item has been approved and processed at our end. Please refer to attached, as a final approver do we still need to also set up purchase Creator or generate Do to complete the task? what are the next steps to complete a purchase request? Yes, you need to have a Purchasing Manager role setup, and all the requisitions will go to them... They need to transfer the requisition into Great Plai...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...

Problem with Executable File generated using Debug Build
Hi All, I am facing a really wierd problem. I am working on a project in which I have implemeted a GUI for controlling laser microscopes. Everything was going fine till I added support for Filter Wheels. The program compiles perfectly fine. Runs perfectly fine if I run it using Microsoft Visual Studios. But When I try to run the program by double clicking on its icon in the debug folder, the program shows that main frame and then hangs. The problem was not there before I added the code for filter wheels. I am placed all the DLL files in the debug folder. Could any body help me out. Regard...

Transform using DoCmd.RunSQL fails
Hi, my transform is failing with ERROR 2342 , How can I redo this... basically I want my "detect" to be the column headers and "values" as rows.. This run fine in query window but I am trying to automate the transform process DoCmd.RunSQL "TRANSFORM First(test.value) AS FirstOfct_num SELECT test.Sample, test.user FROM test GROUP BY test.Sample, test.user PIVOT test.detect;" data below.. Sample User Detect Value 5442 img Test 1 Undetermined 5442 img Test 2 27.7402 5442 img Test 3 33.0166 Thank you -- Message posted via AccessMonster....

Templates #2
In my attempt to fix my generic blank template for a new EXCEL file, I have made it so that, whenever I click on an EXCEL file icon, in addition to opening up the file I want, it also first opens up EXCEL with the "Book 1" blank template. Does anyone know why this is happening and how to stop it? Thanks Dean Look in your XLStart folder and see if you have any other workbooks there. I keep book.xlt (my workbook template) and sheet.xlt (my worksheet template) there. Dean wrote: > > In my attempt to fix my generic blank template for a new EXCEL file, I have > made...

Bug in ActiveDocument.Range.Font?
I'm trying to check whether files contain certain font attributes (hidden, smallcaps, etc.) anywhere within the whole document or in its sections but the code below seems to return wdUndefined (9999999) irrespective of whether the document has any hidden text or no at all. Strangely enough, it works for short test documents but when I try it on a "real" document 20 or 30 pages long, it always returns .Font.Hidden = 9999999 even though running a Find for hidden text shows that the document has no text with hidden font.. It also fails at Section level but works proper...

Find data in two ranges
Hello, I have a worksheet with in the same row two different ranges: one with nummeric data one with time values. These two ranges works as follow the first nummeric data cooresponds with the first time value, the second nummeric data corresponds with the second time value. For the nummerric data I calculated the best of 3. Now I need the corresponding time value for those best of 3. Thanks for your help with this one. Someone replied to your other post and asked for more detail. -- Biff Microsoft Excel MVP "Santafe" <Santafe@discussions.microsoft.com> w...

Why use GetSafeHwnd?
Hi I have a pointer to a window. I need to get the HWND of this window. I can do both: HWND hWnd = pWnd->GetSafeHwnd(); ASSERT(hWnd); or simply: HWND hWnd = pWnd->m_hWnd; ASSERT(hWnd); What's the difference? GetSafeHwnd() returns NULL if m_hWnd is not initialized, while accessing m_hWnd directely on an unattached window would be undefined? Surely this is not the case, because a constructor of a window class initialized m_hWnd = 0 I'd think. So what's the difference then? Lisa "Lisa Pearlson" <no@spam.plz> wrote in message news:O7hfidKtEHA.3152@TK2MSFTN...