pls help - urgent

[angeljj]

please help...
how to set the formula to count the no. of times of each person buying Food or Misc in 2006 or 2007? Thanks

Year Name Food/Misc No. of Times
2007 Peter F 12
2006 Tom F 15
2007 Ann M 15
2007 Peter F 16
2006 Peter M 3
2007 Tom F 2
2006 Ann M 1
2007 Peter M 17
2006 Ann M 18

 

[NBVC]

Try using a Pivot Table

Drop the Year in the Column Field Area
Drop Name and Food/Misc in Row Field Area
Drop No. Of Times in the Data Area.

 

[Oaktree]

Welcome to the board.

Please try to stay away from listing the subject of your post as "please help" or "urgent", as listed in the guidelines for posting (http://www.mrexcel.com/board2/viewtopic.php?t=131687)

Per your question, this is a perfect situation to use a pivot table. Have a look at Excel Help on Pivot Tables if you're not sure how to make one.

 

[PeregrinTook]

Or if you don't want to use pivot tables, array formulae will do the trick - you're only getting this sample cos I'm bored!!!

Book1
	A	B	C	D	E	F	G	H	I	J
1	Year	Name	Food/Misc	No.ofTimes			2006		2007
2	2007	PeterF	F	12			M	F	M	F
3	2006	TomF	F	15		PeterF	0	0	0	28
4	2007	AnnM	M	15		TomF	0	15	0	2
5	2007	PeterF	F	16		AnnM	19	0	15	0
6	2006	PeterM	M	3		PeterM	3	0	17	0
7	2007	TomF	F	2
8	2006	AnnM	M	1
9	2007	PeterM	M	17
10	2006	AnnM	M	18
Sheet2

All the best
PT