Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.
Example:
A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete
Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))
Column C was created using the formula:
=RIGHT(A2,4)
When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1
2="Complete"))
I get a result of 0, it should be 2.
What am I doing wrong???
Thanks Again
--
Rodman Veney
Toppers said:
See Bob's reply:
=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))
Assumes "Jan" is TEXT not date format
:
Let me be a bit more specific.
A spreadsheet contains the following data:
A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold
I would like a count (for example): of the number Complete in Jan of
2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col
C) is
complete then increment a counter - like ANDing 3 countif functions
[i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].
I hope this example bettter expreses the function I am trying to
perform.
Thanks In Advance - Again
--
Rodman Veney
:
I am using Excel 2003 and I would like to count the number of cells
within a
range thar meet multiple criteria. (Note this function is availiable
in 2007
as COUNTIFS).
Can anyone help?
Thanks In Advance