|
count an entry if ????
I know this has probably been addressed but cant locate my specific issue:
I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$1000))
and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$1000)) -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... I know this has probably been addressed but cant locate my specific issue: I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
=SUMPRODUCT(--(DateRange=Date),--ISNUMBER(CashRange))
=SUMPRODUCT(--(DateRange=Date),--ISNUMBER(CheckRange)) Ranges cannot refer to whole columns like A:A in this type of formula. Jerry W wrote: I know this has probably been addressed but cant locate my specific issue: I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
Bob - Thanks for the quick reply. Sorry, I did not mention the entries in b
& c are derived from formulas themselves. Your formula is working except it is counting the formula (I think) within the counted cell so I am coming up with a count of 4 instead of 2. "Bob Phillips" wrote: =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$1000)) and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$1000)) -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... I know this has probably been addressed but cant locate my specific issue: I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
Jerry,
Try this then =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100))) and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($C$1:$C$100))) as Aladin also suggested. -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... Bob - Thanks for the quick reply. Sorry, I did not mention the entries in b & c are derived from formulas themselves. Your formula is working except it is counting the formula (I think) within the counted cell so I am coming up with a count of 4 instead of 2. "Bob Phillips" wrote: =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$1000)) and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$1000)) -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... I know this has probably been addressed but cant locate my specific issue: I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
PERFECTOMUNDO!!!
=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100))) worked just fine. Thank you so much...Jerrry W - Safety Harbor, FL "Bob Phillips" wrote: Jerry, Try this then =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100))) and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($C$1:$C$100))) as Aladin also suggested. -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... Bob - Thanks for the quick reply. Sorry, I did not mention the entries in b & c are derived from formulas themselves. Your formula is working except it is counting the formula (I think) within the counted cell so I am coming up with a count of 4 instead of 2. "Bob Phillips" wrote: =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$1000)) and =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$1000)) -- HTH RP (remove nothere from the email address if mailing direct) "Jerry W" wrote in message ... I know this has probably been addressed but cant locate my specific issue: I would like to count entries in columns b & c that meet criteria in column a. Here is an example: Date Cash Check 01/19/05 2.00 01/19/05 5.00 01/19/05 5.00 01/19/05 3.00 02/28/05 1.00 02/28/05 2.00 What formula would give these counts as the end result?: 01/19/05 2 2 02/28/05 1 1 Thanks - Jerry |
| All times are GMT +1. The time now is 05:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com