Count rows that match criteria in 2 different column cell ranges

I have a spreadsheet that lists all customer orders for the year - each order 
is a row.  I need to count the number of orders per customer per month.

I'm having trouble figuring out what formula to use to count the number of 
rows in which the customer column (a range named "customer") = "xxx" and the  
month column (a range named "month") = 1 (for January). 

I've tried several combinations of things and keep getting errors. I've 
tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that I'm 
thoroughly confused.  I'm sure this is probably simple but I just can't seem 
to see it at this point.

Thanks for your help!
-- 
JoAnn
0
JoAnn (49)
12/6/2005 8:01:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
526 Views

Similar Articles

[PageSpeed] 29

Enter the customer name in D1, and the number of the month in D2, and try
this:

=SUMPRODUCT((customer=D1)*(MONTH(month)=D2))

-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"JoAnn" <JoAnn@discussions.microsoft.com> wrote in message
news:40347DD9-5C35-436E-85C4-10A571BE7993@microsoft.com...
> I have a spreadsheet that lists all customer orders for the year - each
order
> is a row.  I need to count the number of orders per customer per month.
>
> I'm having trouble figuring out what formula to use to count the number of
> rows in which the customer column (a range named "customer") = "xxx" and
the
> month column (a range named "month") = 1 (for January).
>
> I've tried several combinations of things and keep getting errors. I've
> tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that
I'm
> thoroughly confused.  I'm sure this is probably simple but I just can't
seem
> to see it at this point.
>
> Thanks for your help!
> -- 
> JoAnn

0
ragdyer1 (4060)
12/6/2005 9:34:07 PM
JoAnn:  This may sound difficult but, it is really very easy.  Lets assume 
your data looks like this...

Customer	Month
JOE	1
JOE	1
LARRY	1
LARRY	1
LARRY	1
KRISTI	1
JOE	2
HAYDEN	2
LARRY	2
KRISTI	2

You will a title above each column (Customer, Month).  Click on the top, 
left cell of your data, in this case "Customer".  On your toolbar, click 
"Data", then click "Pivot Table and PivotTable Chart".  In the dialogue box 
that appears, click "Next", then "Next" again, then click "Layout".  Drag the 
"Customer" box on the right over to the Row area, then drag the "Month" box 
over to the Row area and drop it under Customer.  Then drag the "Customer" 
box (again, from the right) over to the Data area.  Once you drop it, it 
should read "Count of Customer".  If it doesn't, you can double-click it and 
change its function.  Then click OK.  Now select where you want the Pivot 
Table (a new sheet, or the existing one) and click Finish.  Your result will 
look like this...

Count of Customer		
Customer	Month	Total
HAYDEN	2	1
HAYDEN Total		1
JOE	1	2
	2	1
JOE Total		3
KRISTI	1	1
	2	1
KRISTI Total		2
LARRY	1	3
	2	1
LARRY Total		4
Grand Total		10

So, by month, by customer, you get a count of customers.  Hope this helps.

"JoAnn" wrote:

> I have a spreadsheet that lists all customer orders for the year - each order 
> is a row.  I need to count the number of orders per customer per month.
> 
> I'm having trouble figuring out what formula to use to count the number of 
> rows in which the customer column (a range named "customer") = "xxx" and the  
> month column (a range named "month") = 1 (for January). 
> 
> I've tried several combinations of things and keep getting errors. I've 
> tried ROWS, COUNTIF, IF with COUNTIFs and/or ROWS, etc. to the point that I'm 
> thoroughly confused.  I'm sure this is probably simple but I just can't seem 
> to see it at this point.
> 
> Thanks for your help!
> -- 
> JoAnn
0
JR1 (208)
12/9/2005 5:51:02 PM
Reply:

Similar Artilces:

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Excel-how (find/replace) for multifmat cells w/o changing fmat
For a client, i need to be able to find and replace words in cells with 2 formats. But when you replace them,it changes the format of the whole cell, and not just what you changed. Is there any way to work around this without changing the format after all of the find and replaces? Saved from a previous post, but this one has some wordwrap problems fixed: I think you'd have to keep track of each character's font attributes. This works very slowly, but works: Option Explicit Option Compare Text Type myCharacter myChar As String myLen As Long myName As String myFo...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

1. Selecting field with "enter"; 2. permanently accepting macros
I have recently upgraded to Access 2007 and two characteristics of my former database are not coming through & I want to get them back. First, in Access 2003, I had set it up so that if I hit "enter" the entire field would be selected. Now "enter" causes the cursor to jump to the next field. How can I change this? Secondly, In my database forms, I had incorporated buttons. Now, each time I open the database, I am given a warning about macros and I have to manually tell the program that the macros are trusted. Unfortunately, Access doesn't accept this an...

comparing data #2
I was wondering is there a way to compare data from 2 columns in a side by side comparison. I have a large list of numbers and what I want to do is copy another list of numbers into the spreadsheet. But I want the numbers in cell A1 and B1 to be equal if they are is not a match then leave cell b1 empty. I want that to continue all the way through the entire worksheet. Maurice, Do you want to end up with 2 separate columns, or are you wanting to merge the 2 lists into 1 list, without duplications? Dave I want 2 side by side columns. With the numbers that are equal right next to each othe...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Matching Dates
Ok guys fairly complicated one here... I have a single table with 20,000 transactions within it. Each transaction has a date attached to it. I have another table which contains 3 fields, a StartDate, a FinishDate and a WeekNumber. i.e.: startdate finishdate weeknumber 01/01/2007 08/01/2007 1 What I need to be able to do is take each transactions from the transactions table and link it to this calendar table to get the weeknumber of when each transaction occured. How on earth can I produce this? Many thanks. Ash. hi, ashg657 wrote: > Ok ...

Global address list not updating #2
When I add new accounts and email address they are not showing up in the global address list days and weeks later. I created the id's from my computer and they show up in my global address list, but not on any other machine in my network. If you type in the name it resolves the id, but it does not show up in the GAL. Any ideas? Are you using cached mode? If so, after you make the update, the offline address book has to be generated (check the schedule), and the client has to download the change, which it does only periodically but the user can force by clicking Send/Receiv...

Excluding Rows During Printout
Hi, Is there a way to automatically exclude rows when printing an Excel sheet? I have a list of items (in two colums) in a work sheet similar to this: Task Time ----------- ------- Task A: 1 Task B: 0 Task C: 2 Task D: 0 Task E: 0 Task F: 0 Task G: 4 Task H: 1 When I print the worksheet I only want to print lines where the time cell is not zero. So the printout from the previous data would look like this: Task Time ----------- ------- Task A: 1 Task C: 2 Task G: 4 Task H: 1 Is there a...

change highlight color on active cells
How do I change the active highlight color of an active cell. Louis Only though VBA. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Static OldCell As Range If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone End If Target.Interior.ColorIndex = 6 'yellow Set OldCell = Target End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy and paste into that module. Chip Pearson has a great add-in named Rowliner.xla that will make the highlighting available for all sheets and workbooks. The code above w...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

2 2k7 servers, no owa?
i have 2 e2k7 servers on a test envirnment with mailbox, hub and client access roles on two domain controllers(both global catalog). i'm testing recovery procedures so i shutdown server1, after that i can't access owa on server2 and all exchange services crashed, after starting them, owa times out and load nothing. any ideas? ...

format cells #4
hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'? i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible? thanks. (i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported) -- notrace2004 ------------------------------------------------------------------------ notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817 View this thread: http://www....

Openning Grouped Columns in Protected Sheets
We want to put such a protection to the sheet that, the protection will not prevent the user from openning grouped columns. This is possible only through a macro, for example: Sub a() With Sheet1 .EnableOutlining = True .Protect , True, True, True, True End With End Sub This setting does not get saved with the workbook, so it must be reset by running this macro each time the workbook is opened. -- Jim Rech Excel MVP ...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Converting from Quicken #2
I have just purchased Money Standard and want to convert my quicken data, but it keeps telling me that it can't find all the Quicken files. I have searched teh whole HD for files with the same name as my data file and copied them to the same dir but still no luck. When I download the demo from the web it converts it with no hassles but then I can't get the satandard version to open that file. Any help would be appreciated as I don't want to loose years of work. Thanx In microsoft.public.money, MCLuke wrote: >I have just purchased Money Standard and want to convert my...

exchange migration wizard "data invalid" #2
I am in the process of an Exchange 2003 email migration between 2 forest with an external trust that has been established. I would like to use the Exchange Server Migration tool, but on the screen where you pick the specific mailboxes to move, it gives me an error of "the data is invalid". The specified account does have Full exchange administrator rights. Manolo ...

Summary in the last row
Hi, I'm need to make a vba macro where the macro automaticly shall find the last row and make a summary of the total column D. I cant get It right, can someone please help? So far I only has typed following. Dim lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row Range("D" & lngLastRow + 1) = _ -- Best regards Mia Sub slr() dim mc as long Dim lr As Long mc = 4 'col D lr = Cells(Rows.Count, mc).End(xlUp).Row Cells(lr + 1, mc) = _ Application.Sum(Range(Cells(1, mc), Cells(lr, mc))) End Sub -- Don Guill...

Newbie question / plug in a value for a cell based on a range for that value
Hey guys, New to Excel so sorry if this explanation isn't the clearest in terms of correct terminology. In one section of my spreadsheet I have a list of percentage values associated with numbers 1-20. For example #10 is listed with the percentage value of 38.4% in the cell next to it (O14 and P14). In another section of the spreadsheet I have a quick formula that I'd like to use these %'s based on what number (1-20) is entered. In my spreadsheet the % I want to use for this #10 is listed in P14 (38.4%). What can I plug into my formula so when I enter "10" is kno...

Can we install Rollup 2 for CRM 1.2 Build v1.2.3297.0
Hi, We have a pending upgrade from version 1.2 to v3.0 I was thinking of istalling the rollup for ver 1.2 before upgrading to version 3.0. The current build of the server is v1.2.3297.0 The link below suggests that the build #for rollup 2 1.02.3297.173 Has any body come up with issues when installing the rollup on the 1.2 server (build is 1.2.3297.0) http://support.microsoft.com/default.aspx?kbid=904435# Or is it better to try and directly upgrade to 3.0 rather than putting the roll up and then doing the upgrade? Suggestions Please.... If you are going to upgrade, then I would just ...

Text to Columns 06-01-10
Hi. I have a column where I have names and surnames. I want to put name in one column and surname in anoter column. In order to do this, I use Text to Columns, but I have the following issue: if the cell contains for example Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it assumes there are three surnames, and then put me one word in each column. This is the result, with the example mentioned: A B C D Juan Pérez Juan De los Santos How can I indicate that...

Read values from excel rows
hi I am trying to read the values from a excel row. There are 89 columns for each row and same number of data in each rows. Kindly help regards shantanu shantanu, You need to explain how/where you want to read them.... From another workbook, another worksheet, a VBA function, a macro, a C# application? HTH, Bernie MS Excel MVP "shantanu" <shantanusenin@gmail.com> wrote in message news:1184298794.259390.216180@i38g2000prf.googlegroups.com... > hi > I am trying to read the values from a excel row. There are 89 > columns for each row and same number of da...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

Copy #2
Hi... I have a small problem but still need some help. I have a workbook called "Sumary.xls" located in a folder Dim strPath As String Dim strParent As String Dim pos As Long Dim Slutt As String strPath = ActiveWorkbook.Path **** Then i have to open another workbook called "Statistikk.xls" pos = InStrRev(strPath, "\") strParent = Left(strPath, pos - 1) Slutt = strParent & "\VT" & "\VT" & "\Arkiv\" & "Statistikk.xls" Workbooks.Open Slutt ***Everything is ok with this, but: ***** Then i want to...

Track 1 and 2,Fullz,Skimmer,MSR
USA BANKLOGIN CANADA LOGINS UK LOGINS US CVV UK CVV EU CVV FULL CC with mmn,ssn,dob,pin = pm me for price US Dumps: Canada Dumps: Europe Dumps: Asia Dumps Atm Skimmer Wincor Nixdorf = 3000 Atm Skimmer Wincor = 3000 Atm Skimmer Slimm = 3000 Atm Skimmer Slim = 3000 Atm Skimmer NCR = 3000 Atm Skimmer Diebold Opteva = 2500 Atm Skimmer Diebold = 2000 Atm Skimmer Universal = 4000 Atm Skimmer Small = 2500 Chip POS ingenico&amigo = 1500 MSR206 MSE ETC PRINTED TRACKS ON PLASTIC WITH PIN FOR SALE skimmer-service.com YIM : ha_tuyen6@yahoo.com ICQ : 37630432...