Countif Repeated Value in a Matrix?

B

Bill D. Toronto

I've got a matrix of locations. I want to count each time that the location
appears in the range.
E.g.
ETL London ETO Ottawa ETT Toronto
MTE Edmonton MTN Mountain Time North MTC Calgary
ETT Toronto ETL London CTW Winnipeg
ATF Fredericton ATC Charlottetown ATJ St John's
ATC Charlottetown ATF Fredericton ATH Halifax
ATC Charlottetown ATF Fredericton ATH Halifax

What is the best way to find out how many times that each city appears in
the list? Should I use pivot charts??? or is it an worksheet function that
will do the trick?
Many thanks,
 
V

Vergel Adriano

Bill,

This doesn't seem like exactly what you're looking for, but I hope it helps.
Assuming your matrix is in A1:C6, this will give you the number of cells
with "ETL London"

=COUNTIF(A16:C18,"ETL London")
 
B

Bill D. Toronto

Thanks Vergel,

That worked well for totals. let me throw a monkey wrench into the mix.
What if I wanted to compare multiple values in the same matrix. IE what if I
wanted to count how many times Edmonton appears with Toronto on the same row?
Or let's say I've got another column to add that has nothing to do with the
locations it's merely identifying a value like Yes or No?

Let me know if you need clarification but more importantly... Thanks again!
 
V

Vergel Adriano

Bill,

Assuming your matrix is in A1:B10 and you wanted to know how many rows have
"Edmonton" in column A and "Toronto" in column B.

=SUMPRODUCT((A1:A10="Edmonton")*(B1:B10="Toronto")*1)

You can use named ranges for A1:A10 and B1:B10. The important thing is that
your ranges are equal in size.

If you had a 3rd column that had numeric values for example and you wanted
to add another criteria to get rows where column C > 100 for example, then
your formula becomes:

=SUMPRODUCT((A1:A10="Edmonton")*(B1:B10="Toronto")*(C1:C10>100)*1)
 
B

Bill D. Toronto

Thanks Vergel!
Works like a charm.

Vergel Adriano said:
Bill,

Assuming your matrix is in A1:B10 and you wanted to know how many rows have
"Edmonton" in column A and "Toronto" in column B.

=SUMPRODUCT((A1:A10="Edmonton")*(B1:B10="Toronto")*1)

You can use named ranges for A1:A10 and B1:B10. The important thing is that
your ranges are equal in size.

If you had a 3rd column that had numeric values for example and you wanted
to add another criteria to get rows where column C > 100 for example, then
your formula becomes:

=SUMPRODUCT((A1:A10="Edmonton")*(B1:B10="Toronto")*(C1:C10>100)*1)
Click to expand...
 
Top