Need help reading 2 columns and extracting data under certain conditions

[WhoaBundy]

I have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.

I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this


---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6


Generated from something that looks like this


Red One

Blue Two

Blue Three

Green Two

Some kind of biconditional countif maybe

 

[bertie]

Hi and welcome to the forum.

You can do this using the Sumproduct() function.

Excel Workbook
	A	B	C	D	E	F	G
1	Colour	Number	x	Colour	One	Two	Three
2	Red	One	x	Red	1	1	0
3	Blue	Two	x	Blue	0	1	1
4	Blue	Three	x	Green	0	1	0
5	Green	Two	x
6	Red	Two	x
Sheet1

--($A$2:$A$6=$D2)
The two dashes denote the unary character which tests if the statement is true or false.
So this tests over the specified range in column A to see if a there is a match for the value in D2, i.e., red.

--($B$2:$B$6=$E$1)
This tests for a True/False match for the value in E1, "One" over the specified range in column B.

Combining them together will count count the number of instances where both statements are true and give you the results you are looking for, see the sample data.