| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I put a date range in the criteria of a countif formula?
=COUNTIF('Local Annual Sales'!B4:B19,"=38991") this works to show all
months creater then Oct 2006 how do I limit it to =38991 <=39022 Oct 31, 2006????? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I put a date range in the criteria of a countif formula?
Try one of these:
Use cells to hold your date range: A1 = 10/1/2006 B1 = 10/31/2006 =COUNTIF(B4:B19,"="&A1)-COUNTIF(B4:B19,""&B1) Or, with the dates hardcoded: =COUNTIF(B4:B19,"=10/1/2006")-COUNTIF(B4:B19,"10/31/2006") Format the cell as GENERAL or NUMBER Biff "hlpmelrn" wrote in message ... =COUNTIF('Local Annual Sales'!B4:B19,"=38991") this works to show all months creater then Oct 2006 how do I limit it to =38991 <=39022 Oct 31, 2006????? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I put a date range in the criteria of a countif formula
Thanks Biff I will try that, just to let every one know this can be done
with an formula array by entering this: =SUM(IF('Local Annual Sales'!B4:B19=38991,IF('Local Annual Sales'!B4:B19<=39022,1,0),0)) then pressing CRTL+SHFT+ENT which will put curly brackets around it. If you type the curly brackets it will not work. the =DATEVALUE(01/10/2006) can be use to determine the values between. Just thought I let you know how I fixed it that night. GREAT your =COUNTIF('Local Annual Sales'!B4:B19,"=10/1/2006")-COUNTIF('Local Annual Sales'!B4:B19,"10/31/2006") is much better and it works to. "Biff" wrote: Try one of these: Use cells to hold your date range: A1 = 10/1/2006 B1 = 10/31/2006 =COUNTIF(B4:B19,"="&A1)-COUNTIF(B4:B19,""&B1) Or, with the dates hardcoded: =COUNTIF(B4:B19,"=10/1/2006")-COUNTIF(B4:B19,"10/31/2006") Format the cell as GENERAL or NUMBER Biff "hlpmelrn" wrote in message ... =COUNTIF('Local Annual Sales'!B4:B19,"=38991") this works to show all months creater then Oct 2006 how do I limit it to =38991 <=39022 Oct 31, 2006????? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I put a date range in the criteria of a countif formula
You're welcome. Thanks for the feedback!
Biff "hlpmelrn" wrote in message ... Thanks Biff I will try that, just to let every one know this can be done with an formula array by entering this: =SUM(IF('Local Annual Sales'!B4:B19=38991,IF('Local Annual Sales'!B4:B19<=39022,1,0),0)) then pressing CRTL+SHFT+ENT which will put curly brackets around it. If you type the curly brackets it will not work. the =DATEVALUE(01/10/2006) can be use to determine the values between. Just thought I let you know how I fixed it that night. GREAT your =COUNTIF('Local Annual Sales'!B4:B19,"=10/1/2006")-COUNTIF('Local Annual Sales'!B4:B19,"10/31/2006") is much better and it works to. "Biff" wrote: Try one of these: Use cells to hold your date range: A1 = 10/1/2006 B1 = 10/31/2006 =COUNTIF(B4:B19,"="&A1)-COUNTIF(B4:B19,""&B1) Or, with the dates hardcoded: =COUNTIF(B4:B19,"=10/1/2006")-COUNTIF(B4:B19,"10/31/2006") Format the cell as GENERAL or NUMBER Biff "hlpmelrn" wrote in message ... =COUNTIF('Local Annual Sales'!B4:B19,"=38991") this works to show all months creater then Oct 2006 how do I limit it to =38991 <=39022 Oct 31, 2006????? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Bucketing data based on DATE Range criteria | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| formula to look up and return smallest date from a range of dates | Excel Worksheet Functions | |||
| Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Linear Mode
