Multiple Criteria

[Steve 1962]

Hi

I am attempting to arrive at the answers in cells H4:J6 by using a formula (I think SUMPRODUCT is the answer but I'm having trouble). The raw data is located in three columns that are non-contiguous. I need the count of the items (from each of the two columns) which are allocated to the respective person.

Thanks

SH

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	George		Dog		Dog
2	George		Cat		Bird
3	Tom		Bird		Bird			Dog	Cat	Bird
4	George		Dog		Dog		George	6	1	2
5	Frank		Cat		Cat		Tom	2	1	7
6	Tom		Dog		Cat		Frank	1	4	0
7	Tom		Dog		Bird
8	George		Bird		Dog
9	Tom		Bird		Bird
10	Tom		Bird		Rat
11	Tom		Bird		Rat
12	Frank		Dog		Mice
13	Frank		Cat		Rat
14	George		Rat		Dog
15	Frank		Mice		Cat
16	Frank		Rat		Mice
Sheet1

Excel 2007

 

[kidwispa]

In H4 and copy left and down:

=SUMPRODUCT(--($A$1:$A$16=$G4),--($C$1:$C$16=H$3)+($E$1:$E$16=H$3))

 

[Steve 1962]

Brilliant Kidwispa !!!!

Thanks very much for your quick and helpful reply. All good now.

Have a good weekend.

Steve

 

[kidwispa]

Brilliant Kidwispa !!!!

Thanks very much for your quick and helpful reply. All good now.

Have a good weekend.

Steve

No worries - you too!