Formula that looks at two columns
 

I am trying to determine how many times my colors are associated with
the objects --

Colors Objects

Red Square
Red Circle
Red Square
Yellow Oval
Yellow Star
Yellow Star
Blue Star
Blue Square
Blue Square

For instance, I would like to know how many times Red and Square show
up and how many times Red and Circle show up. Is there a forumula
that will take a look at both columns.

Thanks.

One way:

=SUMPRODUCT(--(A1:A100="Red"),--(B1:B100="Circle"))


If you want to characterize each combination, a Pivot Table, with Colors
in the Row Field, Objects in the Column Field, and Count of Objects in
the data field would be an easy solution. See

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






In article ,
(Ralph Smith) wrote:

I am trying to determine how many times my colors are associated with
the objects --

Colors Objects

Red Square
Red Circle
Red Square
Yellow Oval
Yellow Star
Yellow Star
Blue Star
Blue Square
Blue Square

For instance, I would like to know how many times Red and Square show
up and how many times Red and Circle show up. Is there a forumula
that will take a look at both columns.

Thanks.

One way

=SUMPRODUCT(--(A2:A50="Red"),--(B2:B50="Square"))

to get better functionality you can replace the criteria with cell
references where you put the criteria like

=SUMPRODUCT(--(A2:A50=C2),--(B2:B50=D2))


Regards,

Peo sjoblom

"Ralph Smith" wrote:

I am trying to determine how many times my colors are associated with
the objects --

Colors Objects

Red Square
Red Circle
Red Square
Yellow Oval
Yellow Star
Yellow Star
Blue Star
Blue Square
Blue Square

For instance, I would like to know how many times Red and Square show
up and how many times Red and Circle show up. Is there a forumula
that will take a look at both columns.

Thanks.

Great place for a pivot table. Select all the data, including the "Colors"
and "Objects" column headers and use DataPivot Table. Just click on the
Finish button and then drag one field into the row area, the other field into
the column area, and either field into the data area. That's all there is to
it

"Ralph Smith" wrote:

I am trying to determine how many times my colors are associated with
the objects --

Colors Objects

Red Square
Red Circle
Red Square
Yellow Oval
Yellow Star
Yellow Star
Blue Star
Blue Square
Blue Square

For instance, I would like to know how many times Red and Square show
up and how many times Red and Circle show up. Is there a forumula
that will take a look at both columns.

Thanks.