Counting & Summing based on criteria on another column
 

I have a spreadsheet of estimate date that is incomplete (some cells have
data, some do not yet). I need to be able to count the number of estimates
(col A in the simple example below) that are for completed phases (col
C)...in other words, the date in col C is in the past. I'll also need to be
able sum col A for all completed phases (but this can be done in another
cell). In the example below, I would expect the estimate count to be 2 and
the estimate sum to be 4500.

A B C
1 Estimate Actual Phase Completion Date
2 1000 1200 7/12/2005
3
4 3000 6/30/2005
5 2178 10/1/2005
6 3500 3379 5/14/2004

Any suggestions?

=SUMPRODUCT(--(A2:A100<""),--(C2:C100<TODAY()))

and

=SUMPRODUCT(--(A2:A100<""),--(C2:C100<TODAY()),A2:A100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chicago D" wrote in message
...
I have a spreadsheet of estimate date that is incomplete (some cells have
data, some do not yet). I need to be able to count the number of
estimates
(col A in the simple example below) that are for completed phases (col
C)...in other words, the date in col C is in the past. I'll also need to
be
able sum col A for all completed phases (but this can be done in another
cell). In the example below, I would expect the estimate count to be 2
and
the estimate sum to be 4500.

A B C
1 Estimate Actual Phase Completion Date
2 1000 1200 7/12/2005
3
4 3000 6/30/2005
5 2178 10/1/2005
6 3500 3379 5/14/2004

Any suggestions?

That's it...THANKS!

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A100<""),--(C2:C100<TODAY()))

and

=SUMPRODUCT(--(A2:A100<""),--(C2:C100<TODAY()),A2:A100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chicago D" wrote in message
...
I have a spreadsheet of estimate date that is incomplete (some cells have
data, some do not yet). I need to be able to count the number of
estimates
(col A in the simple example below) that are for completed phases (col
C)...in other words, the date in col C is in the past. I'll also need to
be
able sum col A for all completed phases (but this can be done in another
cell). In the example below, I would expect the estimate count to be 2
and
the estimate sum to be 4500.

A B C
1 Estimate Actual Phase Completion Date
2 1000 1200 7/12/2005
3
4 3000 6/30/2005
5 2178 10/1/2005
6 3500 3379 5/14/2004

Any suggestions?