Conditional COUNTIF Function

[Guest]

Hi, I have two columns with different values in and I would like to count the
number of times certain values appear in the same row.

i.e. Column A contains random numbers from 1-10
Column B contains random letters between A-Z

How could I count the number of times the number "1" is in the same row as
letter "A"??

Struggling at the moment with this so any help would be appreciated.

Thanks

 

[Domenic]

Try...

=SUMPRODUCT(--(A2:A100=1),--(B2:B100="A"))

Adjust the range accordingly.

Hope this helps!

 

[Guest]

Got this from others much smarter than me. Let's say your random numbers are
in the range A1 to A100 and range B1 to B100 contains your letters. To
determine the number of occurrences of rows with the number 1 with the number
A, do this:

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

You could set up two cells to hold the numerical and alphabetical values so
that you don't have to continually modify this formula to see the results of
different combinations. For example, put a number in C1 and a letter in D1
and modify the above formula to:

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

 

[Bruno Campanini]

Hi, I have two columns with different values in and I would like to count
the
number of times certain values appear in the same row.

i.e. Column A contains random numbers from 1-10
Column B contains random letters between A-Z

How could I count the number of times the number "1" is in the same row as
letter "A"??

Struggling at the moment with this so any help would be appreciated.

The shortest formula:
=SUMPRODUCT((A1:A10=5)*(B1:B10="B"))

Bruno