Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
column A has dates, column B has a,b,c,.......
I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
Try
=SUMPRODUCT(--('REAR D'!$C$2:$C$500="10/01/05"), --('REAR D'!$C$2:$C$500<="12/31/05"), --('REAR D'!$B$2:$B$500="F")) for details see on SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
That is probably because it is an array formula, and when you changed it you
didn't re-enter as an array formula. To do so, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
Sorry, I'm still getting zero response figures????
"Bernard Liengme" wrote: Try =SUMPRODUCT(--('REAR D'!$C$2:$C$500="10/01/05"), --('REAR D'!$C$2:$C$500<="12/31/05"), --('REAR D'!$B$2:$B$500="F")) for details see on SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
I'm still getting zero response figures!!?!?!? HELP
"Bob Phillips" wrote: That is probably because it is an array formula, and when you changed it you didn't re-enter as an array formula. To do so, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
Kathi,
I have posted an example at http://cjoint.com/?cikz55RNFI that shows the SUM and the SUIMPRODUCT solutions. Take a look at these and let us know if you still have the problem. BTW, I changed the date style, as yours didn't work for non-US style dates, mine is more generic. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... I'm still getting zero response figures!!?!?!? HELP "Bob Phillips" wrote: That is probably because it is an array formula, and when you changed it you didn't re-enter as an array formula. To do so, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
Thank you so very much. That truly helped. I can't figure out why but when
I first put it in my spread sheet it wouldn't work but for some reason it is working now! Thanks again. I have another question I'm hoping you can help me with. In the same spread sheet I need to know how many days an invoice was open. A is open date and B is closed out date. I need an "IF B is null" added onto the original =+IF(K4<=J4,1,DAYS360(J4,K4)+1) so that if there is a close date then count the number of days between the open date and the close date BUT if there isn't a close date just count the number of days from the open date until the current date. Can you help? "Bob Phillips" wrote: Kathi, I have posted an example at http://cjoint.com/?cikz55RNFI that shows the SUM and the SUIMPRODUCT solutions. Take a look at these and let us know if you still have the problem. BTW, I changed the date style, as yours didn't work for non-US style dates, mine is more generic. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... I'm still getting zero response figures!!?!?!? HELP "Bob Phillips" wrote: That is probably because it is an array formula, and when you changed it you didn't re-enter as an array formula. To do so, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF BETWEEN DATES
Could you explain that a bit more? which is the open date and the close
date, J4 and K4? And what do you mean by added to the original, the formula result of open/close date? BTW, why do you use DAYS360(J4,K4) rather than just K4-J4? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... Thank you so very much. That truly helped. I can't figure out why but when I first put it in my spread sheet it wouldn't work but for some reason it is working now! Thanks again. I have another question I'm hoping you can help me with. In the same spread sheet I need to know how many days an invoice was open. A is open date and B is closed out date. I need an "IF B is null" added onto the original =+IF(K4<=J4,1,DAYS360(J4,K4)+1) so that if there is a close date then count the number of days between the open date and the close date BUT if there isn't a close date just count the number of days from the open date until the current date. Can you help? "Bob Phillips" wrote: Kathi, I have posted an example at http://cjoint.com/?cikz55RNFI that shows the SUM and the SUIMPRODUCT solutions. Take a look at these and let us know if you still have the problem. BTW, I changed the date style, as yours didn't work for non-US style dates, mine is more generic. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... I'm still getting zero response figures!!?!?!? HELP "Bob Phillips" wrote: That is probably because it is an array formula, and when you changed it you didn't re-enter as an array formula. To do so, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "kathi" wrote in message ... column A has dates, column B has a,b,c,....... I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that have an F in column B. I had been using this formula array, for some reason I can't change, alter, copy paste or do anything to it. The input has change but the output hasn't. If I remove the column B reference I still get the correct result of all between those dates, but as soon as I ask for only those with F..... =SUM(('REAR D'!$C$2:$C$500="10/01/05")*('REAR D'!$C$2:$C$500<="12/31/05")*('REAR D'!$B$2:$B$500="F")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Count No. of times Dates are repeated | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
count date occurances in range of dates... | Excel Worksheet Functions | |||
How do I count cells in a column of dates between date ranges? | Excel Worksheet Functions |