Array Help!
Posted by Michelle on October 02, 2001 10:28 AM
I am entering an array to count the number of orders within a time span. I have tried using count and counta. The time span is a concatentation of the year and period, which means it is text. Here is my formula:
=count(if(a2:a1728="199801",b2:b1728))
or
=counta(if(a2:a1728="199801",b2:b1728))
is my formula wrong or is my logic wrong?
Posted by Barrie Davidson on October 02, 2001 10:36 AM
Michelle, try
=COUNTIF(A2:A1728,"199801")
Regards,
Barrie
Barrie Davidson
Posted by Aladin Akyurek on October 02, 2001 10:37 AM
Michelle,
It seems you concatenated year and month to build a criterion. If the range a2:a1278 contains true dates, I'd propose using
=SUMPRODUCT((YEAR(A2:A1278)=1998)*(MONTH(A2:A1278)=1)*(B2:B1728))
This will give you the number of orders in January of 1998.
Aladin
========
Posted by Mark W. on October 02, 2001 10:46 AM
Since COUNTA() will count logical values it cannot
be used for your purposes. That's because
IF(A2:A1728="199801",B2:B1728)) will return FALSE
when A2:A1728 does not equal "199801" and COUNTA
will count 'em.
COUNT() will only work if B2:B1728 contains
numeric values. Is that the case? I trust that
you entering these formulas using the
Control+Shift+Enter key combination. Is that
the case? What results are you getting that
leads you to believe that your "logic is
wrong"?
Posted by Barrie Davidson on October 02, 2001 10:52 AM
Note - this solution is not an array formula (nt)
Posted by Mark W. on October 02, 2001 10:57 AM
More...
If your order numbers are text (e.g., an invoice
number such as "A12034") and blank cells represent
the absence of an order then I'd recommmend that
you employ the following array formula...
{=SUM((A2:A1728="199801")*ISTEXT(B2:B1728))}
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
