Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
=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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
counting based on criteria | Excel Worksheet Functions | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |