counting instances in 2 arrays

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I'm trying to get a formula to work that will count the
number of instances in two columns. For example, I'm
looking to write one formula that will count the number of
Ford Trucks in a spreadsheet that looks like the following:

Column A Column B
Truck Chevy
Car Ford
Truck Ford
Truck Dodge
Car Ford

I've tried different countif functions and can't seem to
get this to count correctly. The resulting answer should
be 1.
 
One way

=COUNTIF(A2:B100,"Ford")

or better

=COUNTIF(A2:B100,C2)

where C2 holds the criteria
 
You can use a sumproduct like;

SUMPRODUCT((A1:A5="Truck")*(B1:B5="Ford"))

This looks through the rows and adds the product of the evaluation of the
equalities. So it looks at A1, and if it's truck, that equals 1. It looks at
B1, and since it's chevy, thats a 0. 1 * 0 = 0

When it gets down to row 3 (ford truck), it says A3=truck = TRUE (true is 1,
false is 0), then looks at B3 and thats TRUE, so 1*1 = 1, and that is added
to the other zeros it evaluated from the list (since the others involved
multiplying 1 * 0 or 0 * 0), and you get a total of 1.
 
Works like a charm - thanks PEO.
-----Original Message-----
Oops! Misread the question,use

=SUMPRODUCT(--(A2:A100="Truck"),--(B2:B100="Ford"))

--

Regards,

Peo Sjoblom





.
Click to expand...
 
Back
Top