Single Function to Analyze Two Columns?

[Guest]

I have a spreadsheet that contains (among other things):
+ codes for car names (1 = Volvo, 2 = Jeep, 3 = Toyota, etc.). These codes
happen to be in Column C. For specific customer-specified reasons, I MUST
use the numeric assignments for car names.
+ codes for car colors (1 = Red, 2 = Blue, 3 = Black, etc.) These codes
happen to be in Column D Again, there is a specific requirement for this.

I know how to count the number of Volvos (=COUNTIF(C:C,"1") and the number
of Red Cars (=COUNTIF(D,"1"). This is needed to develop a grid-like table
with these totals in each grid.

QUESTION: How do I count the number of Red Jeeps, Blue Volvos, etc. without
resorting to VBA? I'm hoping that I can use an Excel function to accomplish
this.

Thanks,
Rich

 

[Guest]

=sumproduct(--(c1:c100=1),--(d1:d100=1))

you can even put this in a data table to generate a grid of makes & colors
(Data>Table..)

 

[Guest]

Hi,

You could try using a SUMPRODUCT Array formula

=SUMPRODUCT(IF($C$2:$C$4=E2,1,0),IF($D$2:$D$4=F2,1,0))

I think with this you can't just specify a column - as this does not handle
empty cells well - here I've assumed that we only have 3 rows of data - from
row 2 to row 4. I've assumed E2/F2 contain the required combo of make/color.

Also you need to commit the formula using ctrl-shift-enter as the IF is
using an array.

Regards,

Chris.