Counting question
 

I'm trying to do something that seems easy, but just don't know how to:

I want to count the number of times column B is not blank AND column A
is a specified value. ex:

A B

C 1
C
C 1
M 1
C
C 1

So I'm looking for the number of times that "C" AND "1" occur.

Any help is greatly appreciated.

Thanks!


--
ckiraly
------------------------------------------------------------------------
ckiraly's Profile: http://www.excelforum.com/member.php...o&userid=25044
View this thread: http://www.excelforum.com/showthread...hreadid=385674

Try...

=SUMPRODUCT(--($A$1:$A$6="C"),--($B$1:$B$6=1))

OR

=SUMPRODUCT(--($A$1:$A$6=D1),--($B$1:$B$6=E1))

...where D1 contains your first criterion, such as C, and E1 contains
your second criterion, such as 1.

Hope this helps!

ckiraly Wrote:
I'm trying to do something that seems easy, but just don't know how to:

I want to count the number of times column B is not blank AND column A
is a specified value. ex:

A B

C 1
C
C 1
M 1
C
C 1

So I'm looking for the number of times that "C" AND "1" occur.

Any help is greatly appreciated.

Thanks!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=385674

Two options...

(a)

=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)

where E2 houses a condition like C and F2 a condition like 1.

(b)

In C2 enter & copy down:

=A2&"#"&B2

Then invoke:

=COUNTIF($C$2:$C$10,E2&"#"&F2)

ckiraly wrote:
I'm trying to do something that seems easy, but just don't know how to:

I want to count the number of times column B is not blank AND column A
is a specified value. ex:

A B

C 1
C
C 1
M 1
C
C 1

So I'm looking for the number of times that "C" AND "1" occur.

Any help is greatly appreciated.

Thanks!



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Thank you both. I will try those solutions out.


--
ckiraly
------------------------------------------------------------------------
ckiraly's Profile: http://www.excelforum.com/member.php...o&userid=25044
View this thread: http://www.excelforum.com/showthread...hreadid=385674

The solutions worked great! Now I want to one up it. How can do this
to see if column B is not blank?


--
ckiraly
------------------------------------------------------------------------
ckiraly's Profile: http://www.excelforum.com/member.php...o&userid=25044
View this thread: http://www.excelforum.com/showthread...hreadid=385674

To count where col A="C" and Col B is not blank change

--($B$1:$B$6=1)

to

--($B$1:$B$6<"")


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=385674

Thank you very much! Slowly but surely I'll get the hang of excel.


--
ckiraly
------------------------------------------------------------------------
ckiraly's Profile: http://www.excelforum.com/member.php...o&userid=25044
View this thread: http://www.excelforum.com/showthread...hreadid=385674