Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
How do I drag a formula from right to left columns instead of rig. | Excel Discussion (Misc queries) | |||
Req Formula to place 1 into next columns | Excel Worksheet Functions |