Counting occurences on multiple criteria

I have a sheet of business data.
There are 3 locations of service and I want to count the numbers o
sales in each location over a given time period.

Column C has the dates of sales
Column H has the location

I tried to get count for sales for the current month of June 2004 a
location A

=SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A"))

and I get 0 (which is wrong.)

I took out the quotes around the date value

=SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A"))

and I get a number which is much too high and is obviously incorrect.

What am I doing wrong

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

0
6/16/2004 10:57:22 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
253 Views

Similar Articles

[PageSpeed] 2

Try this

=SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron H >" <<Ron.H.17yu5k@excelforum-nospam.com> wrote in message
news:Ron.H.17yu5k@excelforum-nospam.com...
> I have a sheet of business data.
> There are 3 locations of service and I want to count the numbers of
> sales in each location over a given time period.
>
> Column C has the dates of sales
> Column H has the location
>
> I tried to get count for sales for the current month of June 2004 at
> location A
>
> =SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A"))
>
> and I get 0 (which is wrong.)
>
> I took out the quotes around the date value
>
> =SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A"))
>
> and I get a number which is much too high and is obviously incorrect.
>
> What am I doing wrong?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
6/16/2004 11:23:55 PM
Thanks for your solution.  I suppose it has something to do wit
SUMPRODUCT seeing the date as text.

By the way, seeing that I needed a number instead of text,I  tried th
following which also worked:

=SUMPRODUCT((C5:C309>(DATEVALUE("05/31/2004")))*(H5:H309="A"))

With regards to my initial formula not working why is it tha
SUMPRODUCT sees (C5:C309>"5/31/2004") as not being a Boolean result a
a numerical  0 or 1?

Ron Hekier






Bob Phillips wrote:
> *Try this
> 
> =SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))
> 
> --
> 
> HTH
> 
> Bob Phillips
> ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
> 
> "Ron H >" <<Ron.H.17yu5k@excelforum-nospam.com> wrote in message
> news:Ron.H.17yu5k@excelforum-nospam.com...
> > I have a sheet of business data.
> > There are 3 locations of service and I want to count the number
> of
> > sales in each location over a given time period.
> >
> > Column C has the dates of sales
> > Column H has the location
> >
> > I tried to get count for sales for the current month of June 200
> at
> > location A
> >
> > =SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A"))
> >
> > and I get 0 (which is wrong.)
> >
> > I took out the quotes around the date value
> >
> > =SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A"))
> >
> > and I get a number which is much too high and is obviousl
> incorrect.
> >
> > What am I doing wrong?
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> > 

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

0
6/17/2004 4:28:40 AM
DATEVALUE works as well, I just prefer --("2004/05/31") as I know it works
in all international settings (MS gets in a real pickle with dates), and I
guess that it is quicker as well using unary operators rather than a
function.

SUMPRODUCT sees (C5:C309>"5/31/2004")  does see them all as Boolean results,
they just all failed so you got zero. Comparing a date against a  text
string will return False.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron H >" <<Ron.H.17z9hq@excelforum-nospam.com> wrote in message
news:Ron.H.17z9hq@excelforum-nospam.com...
> Thanks for your solution.  I suppose it has something to do with
> SUMPRODUCT seeing the date as text.
>
> By the way, seeing that I needed a number instead of text,I  tried the
> following which also worked:
>
> =SUMPRODUCT((C5:C309>(DATEVALUE("05/31/2004")))*(H5:H309="A"))
>
> With regards to my initial formula not working why is it that
> SUMPRODUCT sees (C5:C309>"5/31/2004") as not being a Boolean result as
> a numerical  0 or 1?
>
> Ron Hekier
>
>
>
>
>
>
> Bob Phillips wrote:
> > *Try this
> >
> > =SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> > ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "Ron H >" <<Ron.H.17yu5k@excelforum-nospam.com> wrote in message
> > news:Ron.H.17yu5k@excelforum-nospam.com...
> > > I have a sheet of business data.
> > > There are 3 locations of service and I want to count the numbers
> > of
> > > sales in each location over a given time period.
> > >
> > > Column C has the dates of sales
> > > Column H has the location
> > >
> > > I tried to get count for sales for the current month of June 2004
> > at
> > > location A
> > >
> > > =SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A"))
> > >
> > > and I get 0 (which is wrong.)
> > >
> > > I took out the quotes around the date value
> > >
> > > =SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A"))
> > >
> > > and I get a number which is much too high and is obviously
> > incorrect.
> > >
> > > What am I doing wrong?
> > >
> > >
> > > ---
> > > Message posted from http://www.ExcelForum.com/
> > > *
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
6/17/2004 12:26:53 PM
Reply:

Similar Artilces:

Invoicing with Multiple Bins
Does the invoicing module work with multiple bins enabled ? A client of ours is receiving an error message when trying to enter invoices: "You can't create or post invoices with the multiple bin functionality enabled" Any ideas? If using mulit bins you'll have to use Sales Transaction Entry for Invoicing. The Invoice Entry window does not provide for selection of the bin, only a Site. Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com Thank you, Frank. I had to figure this out the hard wa...

MAX, IF multiples
I have the following formula which works treat. =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis upload'!$O$1:$O$5000))) However, I know need to add more criteria. I need to add, that if coulmn P is blank, return the highest number from column O. i.e the higest number that would be returned is 107, if usuing the same formula, as 122 is populated by column P, so is not part of the equation. Column N Column O Column P BRITISH GASCAR1 183 BRITISH GASCAR0 75 09/06/2006 BRITISH GASCOMM1 106 07/06/2006 BRITISH G...

Xpath with multiple values..
Is is possible to use XPath with multiple values like in SQL Queries (AND Clause).For example: If I have xml: <rows> <row code=1/> <row code=2/> <row code=3/> <row code=5/> <row code=6/> <row code=7/> <row code=100/> </rows> Now I want only to select rows 1,2,3 an 100 XPath("rows/row[@code='1' and @code='2' and @code='3' or @code='100']"). I tryed like this above, but I got nothing. Any examples. Regards. Try using ORs all the way. An AND situation is impossible here...

Rename Multiple Tables at once
I have an Access 2007 database that has a lot of linked tables and I want to 'batch' rename them. For example, they all start with "dbo_" and I want to rename all of them to "PM_". How can I make that happen? I've been searching and cannot find a solution. Any help that anyone can provide is greatly appreciated. Dim dbCurr As DAO.Database Dim tdfCurr As DAO.TableDef Set dbCurr = CurrentDb() For Each tdfCurr In dbCurr.TableDefs If Left(tdfCurr.Name, 4) = "dbo_" Then tdfCurr.Name = "PM_" & Mid(tdfCurr.Name, 5)...

Matching a transaction to Multiple
Hi All, I was wondering if anyone has figured out a solution to this one... I go the bank and deposit a few checks. At home, I record each of the checks because their from different sources/categories, etc. Money downloads a lump sum desposit from the bank and now I try to match to the different transactions. I match them up, and accept them all and so far so good. Until I try to update the accounts again, and the lump sum shows up again. Any idea how to get Money to realize that it downloaded that transaction already and I accepted it? BTW I'm using Money 2005, but have had...

Validation list based on criteria
Hi I want to create a data validation list based on certain conditions. Let me try to explain by example. A1 = oranges B1 = oranges A2 = apples B2 = no record A3 = pears B3 = pears A4 = pears B4 = pears A5 = grapes B5 = grapes A6 = apples B6 = no record B:B value is based on a relative formula determined by the value in A A:A can have duplicate values I want to create a validation list based on B:B where the formula result is “no record” The list needs to be populated with the relative value in A For example: Since B2 and B6 = “no record” then the validat...

setting up multiple profiles
I just installed Outlook 2007. My husband has one comcast account and I have two. We need to be able to use Outlook for these. I have tried to set up my two emails using POP3, but they both come into the same inbox. Please help!!! Thanks, http://www.howto-outlook.com/howto/sortmail.htm For 2007 specific, scroll to bottom "07Jackie66" <07Jackie66@discussions.microsoft.com> wrote in message news:7B3FA5C6-23DD-4314-BB9D-F46CE3E22D1C@microsoft.com... >I just installed Outlook 2007. My husband has one comcast account and I >have > two. We need to be able to...

Outlook 2003 ..multiple instances in the task manager
Very often i find that Outlook is running with mulitple instances. and so i have actually have 3 little notificaiton envelopes in the right hand corner of my desktop, telling me i have new mail. THis is an indicator as well as my not being able to get mail for a second group of email accounts that i send/recieve from periodically. Typically when this happens, i also get an error message when i start up that says my outlook file did not close properly and needs to be checked. I have run scanpst.exe and check and repair and everything seems to be okay. I'm not sure if this is an A...

How do I import multiple vcards in groups into the contacts secti.
I have been using another email program for years called Becky. Now I have office 2003 I am going to change and use outlook so it integrates all in one package. I need to know if it possible to import groups of addresses as I had in the Becky Email or do I have do this as one and then put them back into groups. Also the emails can they be imported to the Outlook. Thanks in advance Jeff ...

Docking Multiple Control Bars on the same side
Hello, I am trying to initiate my application custom made ControlBars docked on the left of the frame but I am not getting the right result: To illustrate what I want to do picture the Microsoft Visual Studio enviroment. Normally you start with a few Control bars docked to the side of the frame. Mine for instance has the "Class View" and then just below the "Properties Window" , they are both docked to the same side one above the other. My problem is iamigne if when you started your MS Visual Studio instead of having both controlBars one above the other, you would...

Sum of data with two criteria
Hi there, i have a (simple!?) problem with the following.. In my sheet, i have 3 columns: column A, containing a order-number column B, containing a quota column C, containing a week-number Now what needs to bee counted, is the SUM of the quota (column B) occurences from a specific order, AND a specific week! Problem is, the rows can contain multiple occurences of an ordernumber... I'm feeling quitte stupid, can anyone help me please? :confused: --- Message posted from http://www.ExcelForum.com/ Hi try =SUMPRODUCT((A1:A1000=order_number)*(C1:C1000=week_number),(B1:B1000)) Fr...

The ability to change multiple activity due dates at once
I would find it extremely helpful when I have multiple activities that are due on the same date and I want to change the due date to be able to change them all at once rather than having to open each one separately and then changing the due date. ---------------- 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&...

Cube View required to count Milestones over time
I have a basic Data Analysis view which counts milestones over time..however I now what to count a breakdown of those milestones over time..I have Milestone types 1,2 and 3. I have created a Milestone Type custom field at task level to set a value against each milestone ie 1, 2 or 3. How do I then get this Milestone Type field into the Cubes in order to add it to the view? Which cube should I be using etc?? ...

Collating entries from multiple sheets
Dear Experts, I have 28 worksheets (drug classes) each with a column O in which drug names will appear if they meet certain criteria. I would like to list these drugs, from the 28 worksheets on one page, as a summary. I want excel to look down column O, for about 100 rows, and collect any occurrences of drug names. In 100 rows drug names may appear 20 times (they will all be unique), the other cells being blank. Can I collect these occurrences on one sheet for summary purposes? regards Martina This should give you some ideas: http://www.rondebruin.nl/copy2.htm Also: ...

print multiple worksheets on one page
I need to be able to combine 4 tabs (worksheets) to print on one page - can this be done? Take a look at this web site - it says it can be done and even shows you how: http://exceltips.vitalnews.com/Pages/T0998_Printing_Multiple_Worksheets_on_a_Single_Page.html Al "Michelle" wrote: > I need to be able to combine 4 tabs (worksheets) to print on one page - can > this be done? Try this, it might be helpful http://office.microsoft.com/en-us/assistance/HA010548301033.aspx "Michelle" wrote: > I need to be able to combine 4 tabs (worksheets) to print on one pa...

Open Multiple Files In New Windows
I can't seem to find an instruction/direction for how to open Excel files in different windows. I want to compare a couple of charts next to each other and every time I open a new file, it opens it into the previous window, forever leaving me with only one file open. Thanks. Bright Spark Both workbooks are open.......you can just see one of them. Got to Window>New Window Then Window>Arrange>Vertical. Gord Dibben MS Excel MVP On Fri, 16 Mar 2007 21:34:41 -0700, Bright Spark <BrightSpark1901@Hotmail.com> wrote: >I can't seem to find an instruction/direction...

COUNT(DISTINCT xx) with OVER PARTITION
I have a table of Visits and wanted to return a subset of all visits in the table along with the distinct count of clients per employee within that subset of rows. I can do it with a subquery or maybe a CTE, but the actual (production) query has a lot of parameters so it really complicates it. I was hoping to use the OVER PARTITION for this, but it errors when I use the DISTINCT on the COUNT. I don't see anything in BOL mentioned about this not being supported. Am I doing something wrong or is this simply something not supported? Using SQL 2008 SP1 CREATE TABLE Visit ...

multiple dependent drop down lists
Hi, I am trying to implement multiple cascading drop down lists. I have two worksheets: a user one (with the dropdown lists and data tobe displayed) and a master one (with variables and data). on the master sheet, variables may be identical in a column but combination of all variables in a row is unique. Here's an example of my data worksheet (I use non standard characters and spaces in each column) var1 var2 var3 data1 data2 data3 A AA AAA 1 2 3 A AA BBB 4 5 6 A BB AAA 7 8 9 B BB AAA 10 11 12 B BB BBB 13...

Linked cells in Multiple Workbooks
I am tracking dates and have linked cells from another workbook so as t not have to duplicate the data entry of dates. The dates are linked t projects that are listed in rows and there is information in m workbook that is additional information within the row. So if someon changes the order of the other workbook, the information that is in m workbook does not get sorted with the order. Is there a way to link m cells to the linked cells? ie. Project ID Start Date Milestone EndDate Person Assigned 1234 (linked) 11/15/04 11/20/04(lin...

Outlook Multiple Profiles at the Same Time
I have two separate icons set up on my desktop to call Outlook using /profiles switch for profiles A & B. I can double click one for A, and it opens my POP3 account for profile A. Then I close it and double click one for B, then it opens my Exchange Server account for profile B. The problem is if I don't close the one for profile A and try to open the one for B, then even if the switch is set to open profile B, it just opens profile A again. Can someone tell me how I can have two separate Outlook for two separate profiles open at the same time? Adding POP3 to my Exchange S...

record multiple invoices to a single deposit
Hello, I was trying to find a way to post several invoices to a single deposit. Is this possible? For example, I receive payment for a $25 and a payment for $45 I will make a deposit to the bank for $70, but in my money register it shows as a $25 and $45 deposit on the same day. I would like for the money register to match the actual deposit amount. Thank you in advance. Here's a link to a previous thread on this issue: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=%23ASSakB2CHA.2644%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3D...

Using Mail Merge with Criteria in a Access query
Hi; In access 2000-2003 it was possible to use the Mail Merge function in Word to access a query that has criteria required for input. You can select the Word document and then the database is opened and the criteria box is opened for input (Enter Parameter Value). This then produces the data for the letters. It used a DDE connection between Word and Access. In Office 2007 the mail merge help document says to go to the Access database and select the query and lists how to link to an existing or new letter. The DDE connection is listed but does not attach to the query? It alway...

count matching numbers
This is probably simple but I'm about to go crazy trying to figure it out. Please help. I'm trying to count or total how many cells match another row of cells. For instance A4 = 23 B4 = 14 A5 = 34 B5 = 30 A6 = 39 B6 = 34 A7 = 48 B7 = 39 A8 = 53 B8 = 55 The answer I'm looking for is 2 because there are two cells that match. (A5 matches B6 and A6 matches B7) Thanks, mike Try ths: =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0)))) -- Biff Microsoft Excel MVP "Catfish" <mtblanton@gmail.com> wrote in message news:491279e0-18f9-4d29-a...

Formula with multiple worksheets
I have multiple worksheets and want to be able to type in a district name in Cell C1-sheet 1 and have it pull the corresponding ISD infomration for payroll, Premium, and fixed cost for years 03-04 from sheet 2 into sheet1 for cells B5 for 03-04 payroll, C5 for 03-04 premiums, and D5 for 03-04 for fixed cost. Also need the formula to do the same thing for 04-05 in sheet 1 from sheet 3 Sheet 1 Cell A B C D 1 2 Payroll Normal Premium Fixed Cost 3 4 2003-04 5 2004-05 Sheet 2 Cell A ...

sort in multiple columns
hi guys, how can i sort lists of names in several columns eg: c3:c15, e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and the last (...Z) end in e31? regards bob Does this mean you have 13+13+14+14=54 names spread over 4 ranges? If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and sort there. Then copy|Paste the rows back to the 4 areas in that range (if you need to). bob wrote: > > hi guys, > how can i sort lists of names in several columns eg: c3:c15, > e3:e15,c18:c31,e18:e31. so that the first names (A.....