Counting based on multiple conditions including date range

I'm going to try to make this dilemma as simple to explain as possible
by using a method we can all relate to :)

Lets say I have the following table, with it being an extract from 
list of what I drank at the pub on certain dates:


Code
-------------------
  
  |------A------|------B------|
  20-Oct-03      Beer
  25-Oct-03      Orange
  13-Nov-03      Beer
  16-Nov-03      Beer
  18-Nov-03      Whiskey
  20-Nov-03      Beer
  27-Nov-03      Gin
  03-Dec-03      Beer
  05-Dec-03      Lemonade
  ...
 
-------------------

Basically, I want a cell to then tell me how many times I had beer i
November

(note - obviously what i'm trying to do is a bit more serious than thi
and contains alot more data, but I thought this would be a good way t
explain!

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

0
5/13/2004 4:59:40 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
304 Views

Similar Articles

[PageSpeed] 6

Hi
a formula approach:
=SUMPRODUCT(--(MONTH(A1:A100)=11),--(YEAR(A1:A100)=2003),--(B1:B100="Be
er"))

You may also consider using a pivot table for this. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


> I'm going to try to make this dilemma as simple to explain as
> possible, by using a method we can all relate to :)
>
> Lets say I have the following table, with it being an extract from a
> list of what I drank at the pub on certain dates:
>
>
> Code:
> --------------------
>
>   |------A------|------B------|
>   20-Oct-03      Beer
>   25-Oct-03      Orange
>   13-Nov-03      Beer
>   16-Nov-03      Beer
>   18-Nov-03      Whiskey
>   20-Nov-03      Beer
>   27-Nov-03      Gin
>   03-Dec-03      Beer
>   05-Dec-03      Lemonade
>   ...
>
> --------------------
>
> Basically, I want a cell to then tell me how many times I had beer in
> November
>
> (note - obviously what i'm trying to do is a bit more serious than
> this and contains alot more data, but I thought this would be a good
> way to explain!)
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
5/13/2004 5:10:20 PM
That worked just great. Thank you very much

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

0
5/13/2004 6:01:04 PM
Reply:

Similar Artilces:

My report contains multiple copies of each record
I have three tables (parent/guardian, children, otheradults). that are all related with left joins (i.e. Include ALL records from 'Parent/Guardian' and only those records from 'Adults' where the joined fields are equal.) by ContactID (which is the parent/guardian ID) same for children. I created a query called Family which successfully combined three queries of each table where I combined the first and last names into one field. When I create my report, it creates an entry for EACH child rather than listing the children and related adults under Parent/Guardia...

moving Exchange 2003 mailboxes from multiple domains to one new do
My compnay has Exchange servers in three different domains. We have built a new domain and want to move all mailboxes into one exchange server in the new domain. We have let mailbox size get out of hand and some users have 2GB mailbox size. exporting old mailbox data to PST and importing into new exchange server is possible but would take forever (hundreds of users). any ideas how to streamline this process? or an alternate method of attack? On Wed, 28 Mar 2007 08:24:06 -0700, Kemper <Kemper@discussions.microsoft.com> wrote: >My compnay has Exchange servers in three different d...

Entity Condition in Workflow Manager
I am trying to use "Product" as a condition in a workflow rule. I want to assign the case to a particular queue based upon the Product. I added Product to Case creation form, but it is not showing up as a valid "Field" when creating the Condition. How do I add it into this list as well? Thanks. What's really weird is that if I create a "Custom Attribute" for cases, it shows up as a valid conidition????? "JS" wrote: > I am trying to use "Product" as a condition in a workflow rule. I want to > assign the case to a particu...

Multiple Owners: One Territory
We have support staff that works witih the territory owner. Sharing accounts in the territory is impossible to keep up with. I'd have to manually share all accounts in the territory each month we upload new sales data. (we have at least 150K accounts) Is there any way to associate a territory to multiple owners? ...

Combo boxes and linked ranges
I have created a spread sheet for printing and storing dog show information I have created a combo box to call up the various show venues from a named range. Is it possible to somehow link the 'secretary', 'address etc' columns to appear in the appropriate postions as indicted on the screen shot Ted Cullen [image: http://www.users.bigpond.net.au/reviver/excel.jpg] --- Message posted from http://www.ExcelForum.com/ Hi Yes, it's is. Use VLOOKUP function for it. With additional information in adjacent columns to show venues list (from where you defined the named range...

New discussion to increment the count, bwahahah
yeah ...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

Counting with 2 Conditions
I created a report and would like to get a count of records with two conditions. Here is what I entered: =Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") This information is entered in an unbound text. I would like to get a total count where the terminate field (yes/not) is checked and the caseload meets a certain criteria. Thank you! TomP wrote: >I created a report and would like to get a count of records with two >conditions. Here is what I entered: > >=Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") > >This information is ent...

Offset in Conditional Formatting?
I have the following formula to check for a condition: =IF(CB3="Reg",MOD(CA3-BZ3,1)>TIME(0,$AG$39,0),IF(CB4="Reg",MOD(CA4-BZ4,1)>TIME(0,$AG$39,0),IF(CB5="Reg",MOD(CA5-BZ5,1)>TIME(0,$AG$39,0)))) Essentialy what it does is check if a value (Reg) is present in one of 3 sequential rows and if so calculates if the time difference between two related values in the same row are greater than a predetermined number. If TRUE then the cell is formatted. The formula works fine for a single cell but when I use the Applies To field in CF and highlight th...

Change Label based on Two Combo Boxes
Hi, I currently have this code in AfterUpdate event for Combo2, hoping it would change label1, but it does not work. : If Me.Combo1.Value = "State" And Me.Combo2.Value = "2008" Or "2007" Or "2006" Then Me.Label1.Caption = "Select a State:" ElseIf Me.Combo1.Value = "City" And Me.Combo2.Value = "2009" Or "2010" Or "2011" Then Me.Label1.Caption = "Select a City:" Else Me.Label1.Caption = "Select a Number:" End If The label does not change when I make changes to the combo...

Java vs. C++ (Date class)
I'm porting some Java code into C++. Which class I should use instead Date class? Note that Date support miliseconds. Also, which class I should use instead of Vector class? std::vector? "Petar Popara" <my.fake@mail.net> wrote in message news:uG6VGpUfFHA.2424@TK2MSFTNGP09.phx.gbl... > > I'm porting some Java code into C++. Which class I should use instead Date > class? Note that Date support miliseconds. Oh dear. Both the Java and the C++ date classes do assorted odd things with time zones and "daylight saving"; neither is documented fully or ...

looping across columns in range?
Can someone help me here, we have the following VBA code: Sub TrafficLight() Dim R As Integer Dim Pcent As Double Pcent = 0.5 For R = 9 To 384 ' note the number range If Range("BF" & R).Value = "-" Then Range("BF" & R).Interior.Color = vbWhite ' Greater than 5% less Else If Range("BF" & 385).Value * (1 + Pcent) < Range("BF" & R).Value Then Range("BF" & R).Interior.Color = vbRed Else ' greater than 5% more If Range("BF" & 385).Value > Range("BF" & R).Value * (1 + Pcent)...

Want to find max in a range then return a name from a cell
The spreadsheet has 5 people each person has a list describing thei sales performance. Each has their highest sale, highest over cost sale etc. I have a table with a describtion highest sale, highest over cost sal etc. I want the cell beside each title to calcute who has the highest figur and place their name in this cell. :confused: I have a spreadsheet with lists containing highest sale highest average sale etc. What I want to do is create a formula tha will find the max value within a list please note these values ar spread out and do not run on the spreadsheet side by side or one aft...

how to create a calendar to select a date in access 2007
I want to select a date from a pop up calendar on the form. Access 2007 automatically adds a date picker to a Date/Time field. -- Lynn Trapp MCP, MOS, MCAS "keithteri" wrote: > I want to select a date from a pop up calendar on the form. ...

Sharing contact folder for multiple machines?
I have 3 WinXP machines connected to a network via a router. Is there a way to share the contact folder for everyone's Outlook? That way, a contact change need not be done for every machine. See if the information on the following page helps: http://www.slipstick.com/outlook/share.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:OWYWuxkAEHA.712@tk2msftngp13.phx.gbl, tiki2k wrote: > I have 3 WinXP machines connected to a network via a router. Is the...

Creation date
Hi, I would like a way to insert the creation date of worksheet, not the creation date of the template. -- Thank you in Advance Merci a l'Avance Martin Hi you'll need VBA for this. Find below a UDF for this task: Public Function Get_Creation_date() As Date Get_Creation_date = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") End Function Enter the following in your target cell: =Get_Creation_date() and format the result as date -- Regards Frank Kabel Frankfurt, Germany Martin Racette wrote: > Hi, > > I would like a way to insert the creation d...

Crosstab with Multiple data fields
I would like to make a crosstab query that can have multiple data fields such as ProdCode AcctNo SalesAmt SalesTran RetAmt RetTran I know how to do this with a pivot table but I am required to have it in Access. I can also do this with just one data field such as SalesAmt---I just don't know how to add more than one data field. Is there some workaround to this problem? Thanks, Billy Post a sample of you raw data indicating field names and how you want the results to look like. -- KARL DEWEY Build a little - Test a little "billy.rogers@chasepaymentech.com&...

Conditional filter by statecode
Hi: I'm trying to filter all wfprocess entities by EntityType, StateCode & ProcessTypeCode using: condition1.AttributeName = "entitytype"; condition1.Operator = ConditionOperator.Equal; condition1.Values = new string [] {entityfilter}; condition2.AttributeName = "processtypecode"; condition2.Operator = ConditionOperator.Equal; condition2.Values = new string [] {"-1"}; condition3.AttributeName = "statecode"; condition3.Operator = ConditionOperator.Equal; condition3.Values = new string [] {"1"}; .... The third condition to filter st...

Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et
Hi, If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date format from "Format Cells-- Number tab--Category--choose "date" or "custom" but just failed. While Strangely, If i imput the data as "2004-12-31", i can change it to whatever date format i like. Appreicate your advice at earliest convenience. Thanks -- wintersunshine Hi sounds like your imported dates are not recognised as 'date' values but are stored as 'Text&...

Convert text to date 12-21-07
Hello, I am trying to link to a text file. I was having trouble converting 20011975 into a date/time format but it wasn't working. So I formatted the field as a text file. I have tried converting this to 20/01/1975 in a query but it comes out with errors and wierd dates. I've had a look at the other messages and had a go but no go. thanks Regardless of how your dates are displayed either by formatting or based on regional settings, all date functions exppect a month day year format. To convert your string to a date field: strTextDate = "20011975" SomeDate = DateSer...

Stop multiple cells from being automatically selected
When I place the cursor in a cell, it selects that cell and the 3 directly below it. Why is this happening and how do I stop it? I have already exited the program and even rebooted. Thanks. hi if you are using 03 or earlier, try tapping the F8 key. this is the extended select mode. it's boolean. tap once to enter extended mode, again to exit. if you are useing 07, this is a bug. try adjusting your zoom. see this....http://tinyurl.com/3wofsf regards FSt1 "JDandLinda" wrote: > When I place the cursor in a cell, it selects that cell and the 3 directly > ...

CRM4
Hello Im trying to create a service activity using a workflow - i have chosen multiple dynamic values for several fields, like Location {Address 1: Street 1(Customer (Account));Address 1: City(Customer (Account));Address 1: ZIP/Postal Code(Customer (Account))} but only the first value - the street1 shows up on the Service Calendar - not all of them. any ideas? thanks! james ...

A looping condition has occurred
I'm hoping someone can help me solve this issue, as i've=20 tried other suggested solutions and havent resolved it.=20 This is the error message i get in the event log: A non-delivery report with a status code of 5.3.5 was=20 generated for recipient rfc822;president@mydomain.net=20 (Message-ID <20040224082819.C1FA557F41@vi7.org>). =20 Causes: A looping condition was detected. (The server is=20 configured to route mail back to itself). If you have=20 multiple SMTP Virtual Servers configured on your Exchange=20 server, make sure they are defined by a unique incoming=20 port and ...

Keep a date from updating
Hi there. When I run my macro it populates cell A1 with todays date, the problem is the next day the date changes to the current day. I would like the date to stay at the date the macro is run then the next day it keeps the date etc. so I can look back in the month and see the correct date. Please help , am I missing soemthing simple here? Convert the formula [I expect it is =TODAY() ] to a value within the macro with these three lines of code Range("A1").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=...

Date shows as 01/01/00
We have a spreadsheet that we want to enter a date in a cell. I have changed the format to Date 03/01/01 format but no matter what I do, any date is converted to 01/01/00. I have tried inserting a column, copying to another spreadsheet, etc. I know this is probably simple, but it's driving me crazy. Help please. In an un-used cell enter: =TODAY() if this is not the correct date, then touch CNTRL-ALT-F9 on the keyboard post what happens. -- Gary''s Student "Kmagg" wrote: > We have a spreadsheet that we want to enter a date in a cell. I have changed > the...