| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records between dates
Glad it worked for you.
Heliocracy wrote: You're a genius. Thanks. "Dave Peterson" wrote: Because excel won't let you <bg. But you could stop looking where you want: =SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),--(left(O2:O100,5)="Maint")) or if "maint" could be anywhere in that cell: =SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"), --(ISNUMBER(SEARCH("maintenance",O2:O100)))) =search() doesn't care about case. =Find() is case sensitive. Heliocracy wrote: Thanks to you both, this appears to produce the correct answer. Why can't I use a wildcard in the "Maintenance" to allow for misspelling or spaces after the end of the word? Thanks "Dave Peterson" wrote: It may be easier to use the month number (mm) instead of month name (mmmm). It'll be easier to avoid typos <vbg. =SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),--(O2:O100="Maintenance")) Teethless mama wrote: =SUMPRODUCT(--(TEXT(A2:A100,"mmmm yyyy")="Octobober 2006"),--(O2:O100="Maintenance")) "Heliocracy" wrote: For each record, Column B is a date and Column O is either "Maintenance" or "Routine." How do I count the number of "Maintenance" records whose dates fall within a specific month--ex: count number of records that are dated anytime in October 2006 (in column B) AND have the word "Maintenance" (in column O)? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count of different type records | Excel Worksheet Functions | |||
| Number of records between dates | Excel Discussion (Misc queries) | |||
| Count Records Between A-K and L-Z | Excel Worksheet Functions | |||
| Get count of records for a particular month and year | Excel Discussion (Misc queries) | |||
| count the no.of records between two dates | Excel Worksheet Functions | |||
Hybrid Mode
