software

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$500gt;=quot;10/01/05quot;)*('REAR
D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
Try
=SUMPRODUCT(--('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;),
--('REAR gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;), --('REAR D'!$B$2:$B$500=quot;Fquot;))for details see on SUMPRODUCT
www.xldynamic.com/source/xld.SUMPRODUCT.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;kathiquot; gt; wrote in message
...
gt; column A has dates, column B has a,b,c,.......
gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that
gt; have an F in column B.
gt; I had been using this formula array, for some reason I can't change,
gt; alter,
gt; copy paste or do anything to it. The input has change but the output
gt; hasn't.
gt; If I remove the column B reference I still get the correct result of all
gt; between those dates, but as soon as I ask for only those with F.....
gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt;
gt;
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)

quot;kathiquot; gt; wrote in message
...
gt; column A has dates, column B has a,b,c,.......
gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that
gt; have an F in column B.
gt; I had been using this formula array, for some reason I can't change,
alter,
gt; copy paste or do anything to it. The input has change but the output
hasn't.
gt; If I remove the column B reference I still get the correct result of all
gt; between those dates, but as soon as I ask for only those with F.....
gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt;
gt;
Sorry, I'm still getting zero response figures????

quot;Bernard Liengmequot; wrote:

gt; Try
gt; =SUMPRODUCT(--('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;),
gt; --('REAR gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;), --('REAR D'!$B$2:$B$500=quot;Fquot;))
gt;
gt;
gt; for details see on SUMPRODUCT
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt; column A has dates, column B has a,b,c,.......
gt; gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that
gt; gt; have an F in column B.
gt; gt; I had been using this formula array, for some reason I can't change,
gt; gt; alter,
gt; gt; copy paste or do anything to it. The input has change but the output
gt; gt; hasn't.
gt; gt; If I remove the column B reference I still get the correct result of all
gt; gt; between those dates, but as soon as I ask for only those with F.....
gt; gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt; gt;
gt; gt;
gt;
gt;
gt;

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

quot;Bob Phillipsquot; wrote:

gt; That is probably because it is an array formula, and when you changed it you
gt; didn't re-enter as an array formula. To do so, it should be committed with
gt; Ctrl-Shift-Enter, not just Enter.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt; column A has dates, column B has a,b,c,.......
gt; gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those that
gt; gt; have an F in column B.
gt; gt; I had been using this formula array, for some reason I can't change,
gt; alter,
gt; gt; copy paste or do anything to it. The input has change but the output
gt; hasn't.
gt; gt; If I remove the column B reference I still get the correct result of all
gt; gt; between those dates, but as soon as I ask for only those with F.....
gt; gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt; gt;
gt; gt;
gt;
gt;
gt;

Kathi,

I have posted an example at 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)

quot;kathiquot; gt; wrote in message
...
gt; I'm still getting zero response figures!!?!?!? HELP
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; That is probably because it is an array formula, and when you changed it
you
gt; gt; didn't re-enter as an array formula. To do so, it should be committed
with
gt; gt; Ctrl-Shift-Enter, not just Enter.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;kathiquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; column A has dates, column B has a,b,c,.......
gt; gt; gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those
that
gt; gt; gt; have an F in column B.
gt; gt; gt; I had been using this formula array, for some reason I can't change,
gt; gt; alter,
gt; gt; gt; copy paste or do anything to it. The input has change but the output
gt; gt; hasn't.
gt; gt; gt; If I remove the column B reference I still get the correct result of
all
gt; gt; gt; between those dates, but as soon as I ask for only those with F.....
gt; gt; gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; gt; gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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 quot;IF B is nullquot;
added onto the original = IF(K4lt;=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?

quot;Bob Phillipsquot; wrote:

gt; Kathi,
gt;
gt; I have posted an example at cjoint.com/?cikz55RNFI that shows the SUM
gt; and the SUIMPRODUCT solutions.
gt;
gt; Take a look at these and let us know if you still have the problem.
gt;
gt; BTW, I changed the date style, as yours didn't work for non-US style dates,
gt; mine is more generic.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;kathiquot; gt; wrote in message
gt; ...
gt; gt; I'm still getting zero response figures!!?!?!? HELP
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; That is probably because it is an array formula, and when you changed it
gt; you
gt; gt; gt; didn't re-enter as an array formula. To do so, it should be committed
gt; with
gt; gt; gt; Ctrl-Shift-Enter, not just Enter.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;kathiquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; column A has dates, column B has a,b,c,.......
gt; gt; gt; gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only those
gt; that
gt; gt; gt; gt; have an F in column B.
gt; gt; gt; gt; I had been using this formula array, for some reason I can't change,
gt; gt; gt; alter,
gt; gt; gt; gt; copy paste or do anything to it. The input has change but the output
gt; gt; gt; hasn't.
gt; gt; gt; gt; If I remove the column B reference I still get the correct result of
gt; all
gt; gt; gt; gt; between those dates, but as soon as I ask for only those with F.....
gt; gt; gt; gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; gt; gt; gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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)

quot;kathiquot; gt; wrote in message
...
gt; Thank you so very much. That truly helped. I can't figure out why but
when
gt; I first put it in my spread sheet it wouldn't work but for some reason it
is
gt; working now! Thanks again. I have another question I'm hoping you can
help
gt; me with. In the same spread sheet I need to know how many days an invoice
gt; was open. A is open date and B is closed out date. I need an quot;IF B is
nullquot;
gt; added onto the original = IF(K4lt;=J4,1,DAYS360(J4,K4) 1) so that if there
is a
gt; close date then count the number of days between the open date and the
close
gt; date BUT if there isn't a close date just count the number of days from
the
gt; open date until the current date. Can you help?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Kathi,
gt; gt;
gt; gt; I have posted an example at cjoint.com/?cikz55RNFI that shows the
SUM
gt; gt; and the SUIMPRODUCT solutions.
gt; gt;
gt; gt; Take a look at these and let us know if you still have the problem.
gt; gt;
gt; gt; BTW, I changed the date style, as yours didn't work for non-US style
dates,
gt; gt; mine is more generic.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;kathiquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I'm still getting zero response figures!!?!?!? HELP
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; That is probably because it is an array formula, and when you
changed it
gt; gt; you
gt; gt; gt; gt; didn't re-enter as an array formula. To do so, it should be
committed
gt; gt; with
gt; gt; gt; gt; Ctrl-Shift-Enter, not just Enter.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;kathiquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; column A has dates, column B has a,b,c,.......
gt; gt; gt; gt; gt; I need to count by Fiscal Year Quarters (10/01/05-12/31/05)only
those
gt; gt; that
gt; gt; gt; gt; gt; have an F in column B.
gt; gt; gt; gt; gt; I had been using this formula array, for some reason I can't
change,
gt; gt; gt; gt; alter,
gt; gt; gt; gt; gt; copy paste or do anything to it. The input has change but the
output
gt; gt; gt; gt; hasn't.
gt; gt; gt; gt; gt; If I remove the column B reference I still get the correct result
of
gt; gt; all
gt; gt; gt; gt; gt; between those dates, but as soon as I ask for only those with
F.....
gt; gt; gt; gt; gt; =SUM(('REAR D'!$C$2:$C$500gt;=quot;10/01/05quot;)*('REAR
gt; gt; gt; gt; gt; D'!$C$2:$C$500lt;=quot;12/31/05quot;)*('REAR D'!$B$2:$B$500=quot;Fquot;))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;