count
 

I am trying to get a count of how many times a number appears In colume A
that is a check in colume B. What formula do I use ?

Colume A colume B
$140 check
$30 Pass
$140 Pass
$115 Check
$75 Check
$30 check
$140 check

Answer:

$140 2
$115 1
$30 1
$75 1

count
 

Try this:

=SUMPRODUCT(--(A1:A10=140),--(B1:B10="check"))

Or, if you have the unique numbers listed in a range, say D1:D5

=SUMPRODUCT(--(A$1:A$10=D1),--(B$1:B$10="check"))

Then copy down as needed


--
Biff
Microsoft Excel MVP


"Ros" wrote in message
...
I am trying to get a count of how many times a number appears In colume A
that is a check in colume B. What formula do I use ?

Colume A colume B
$140 check
$30 Pass
$140 Pass
$115 Check
$75 Check
$30 check
$140 check

Answer:

$140 2
$115 1
$30 1
$75 1

count
 

Ros,

I placed the following formula into column C:
=SUMPRODUCT(--($B$1:$B$7=B1),--($A$1:$A$7=A1))

Pull this formula down to return the Count for each criteria, A:B. So,
140:Check, 30:Pass, etc. If you wanted to amend this to only show the Check,
change B1 to "Check" and only for $140, change A1 to 140.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"Ros" wrote:

I am trying to get a count of how many times a number appears In colume A
that is a check in colume B. What formula do I use ?

Colume A colume B
$140 check
$30 Pass
$140 Pass
$115 Check
$75 Check
$30 check
$140 check

Answer:

$140 2
$115 1
$30 1
$75 1