COUNT IF BETWEEN DATES

[Guest]

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"))

 

[Bernard Liengme]

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

 

[Bob Phillips]

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)

 

[Guest]

Sorry, I'm still getting zero response figures????

 

[Guest]

I'm still getting zero response figures!!?!?!? HELP

 

[Bob Phillips]

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)

 

[Guest]

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]

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)