Countif

[David]

Is there an easy way to count items in a column ONLY if
the value in ANOTHER column equals something specific.
I've used a DCount in the past, but this is somewhat
unwieldy. Any ideas?

 

[Frank Kabel]

Hi David
try something like
=SUMPRODUCT((A1:A100=value1)*(B1:B100=value2))

 

[David]

Frank,

Thanks so much for your reply. Forgive me, but I'm still
a little unsure how to proceed. What should be placed as
Value1 and value2? (Is one of these my "specific value"
that I wanted to count in the 2nd column? If so, what is
value2?) Should any part of this be entered as an array?

Thanks again Frank.

-David

 

[Frank Kabel]

Hi Dave
I wasn't sure if you ahd two condition or only one. If you have only
one condition you could use
=COUNTIF(A:A,your_specific_value)

 

[David]

Yea....the problem with that is I only want to count (for
example)the item in column A, row 1 if Column B, Row 1
contains the text "CountMe".

SUMIF allows this flexibility because it includes a
criteria and range, as well as a "sumrange".

This make sense?

-David

 

[Frank Kabel]

Hi David
why not use
=COUNTIF(B:B,"CountMe")

 

[David]

Imagine the following spreadsheet:
A B C
CountMe 10 3
NoCount 6 5
CountMe 3


The CountIF in column B would return 1. The COUNTIF in
column C would return 2.

 

[Frank Kabel]

O.K.
try
=SUMPRODUCT(--(A1:A100="CountMe"),--(B1:B100<>""))

 

[David]

Frank

Awesome...works perfectly. Thanks so much. By the
way...why the two hyphens before the ranges? I'm curious
as to how it works.

Thanks again Frank!

-David

 

[Frank Kabel]

Hi
these unary operator (double minus) corerces the boolean values
(TRUE/FALSE) to numbers (1/0)

 

[David]

Thanks Frank!!

-----Original Message-----
Hi
these unary operator (double minus) corerces the boolean values
(TRUE/FALSE) to numbers (1/0)

--
Regards
Frank Kabel
Frankfurt, Germany



.