Count rows with 2 define values

[catlair]

Hi,

I have unsuccessfully used "COUNTIF" to count the number of rows with 2
dissimilar values (A+B). If the row does not have both values then I
hope to skip/exclude that row from the count i.e. only wish to count
the rows with both values. Is there a macro for this?

catlair

 

[jpdphd]

Here's an easy formula to do it.

The range you want to examine is A1:A8 and you want to count 1's and
2's

=COUNTIF(A1:A8,1)+COUNTIF(A1:A8,2)

 

[JE McGimpsey]

I have unsuccessfully used "COUNTIF" to count the number of rows with 2
dissimilar values (A+B). If the row does not have both values then I
hope to skip/exclude that row from the count i.e. only wish to count
the rows with both values. Is there a macro for this?

No need for a macro:

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[catlair]

Thanks! Works great!

catlair

No need for a macro:

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Jay]

No need for a macro:

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

Thanks for the pointer to the site John. It's a really useful
explantion. I'm making it one of my goals to master array formulas, and
consequently the mysterious, wonderful uses of SUMPRODUCT.

I'm a little confused by the explanatory formula used in the page you
quote. Basically, I'm trying to work out when I'd need to use
SumProduct. Can you suggest any other information resources

Thanks,

Jay

 

[JE McGimpsey]

I'm a little confused by the explanatory formula used in the page you
quote. Basically, I'm trying to work out when I'd need to use
SumProduct. Can you suggest any other information resources

The technique shown in the example is one where multiple conditions need
to be met before counting or summing - the multiplication of
conditionals acts like a logical AND.

Other times it's useful are when you want a sum of products but don't
want to use a helper column to get the products first.