COUNTIF using 2 rows?

[Jason O]

Hi

I'm been trying to do something and think I may be barking up the wrong tree
by trying to use COUNTIF. Let's say I have 2 columns - car manufacturer &
model (i.e Ford/Vauxhall & Fiesta/Astra) How can I count the no. of entries
of each combination of data - i.e how many Ford Fiestas/Focus, how many
Vauxhall Astra/Corsa?

I was trying COUNTIF but think this may be wrong.

Any help greatly appreciated.

TIA,

Jason
____

 

[Domenic]

Assuming that Column A contains the manufacturer, and Column B contains
the model, try...

=SUMPRODUCT(--($A$2:$A$100="Ford"),--($B$2:$B$100="Focus"))

or

=SUMPRODUCT(--($A$2:$A$100=D2),--($B$2:$B$100=E2))

....where D2 contains the manufacturer, such as Ford, and E2 contains the
model, such as Focus.

Hope this helps!

 

[JE McGimpsey]

Hi

I'm been trying to do something and think I may be barking up the wrong tree
by trying to use COUNTIF. Let's say I have 2 columns - car manufacturer &
model (i.e Ford/Vauxhall & Fiesta/Astra) How can I count the no. of entries
of each combination of data - i.e how many Ford Fiestas/Focus, how many
Vauxhall Astra/Corsa?

I was trying COUNTIF but think this may be wrong.

This is the perfect application for a Pivot Table. See

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Jason O]

This is the perfect application for a Pivot Table. See

http://peltiertech.com/Excel/Pivots/pivotstart.htm

I started using a pivot table but need averages of my other columns (price &
mileage) & thought that pivot tables just summed rather than averaged. Is
there a simple way of changing the pivot table function from sum to average?

Many thanks.

Jason
___

 

[JE McGimpsey]

This is the perfect application for a Pivot Table. See

http://peltiertech.com/Excel/Pivots/pivotstart.htm

I started using a pivot table but need averages of my other columns (price &
mileage) & thought that pivot tables just summed rather than averaged. Is
there a simple way of changing the pivot table function from sum to average?[/QUOTE]

One way:

Select the Sum field. Choose Field Settings from the Pivot Table
dropdown in the PT toolbar. Choose Average, then click OK.