| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
=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 |
|
#3
|
|||
|
|||
|
=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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
| Filter Count of Records Retrieved. | Excel Discussion (Misc queries) | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
| Count number to reach a cumulative value | Excel Worksheet Functions | |||
| How to you count cells that contain any entry? | Excel Discussion (Misc queries) | |||
Linear Mode
