pls help - urgent

angeljj

New Member
Joined
Sep 6, 2006
Messages
1
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

Board Regular
Joined
Feb 9, 2006
Messages
157
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!!! :LOL:
Book1
ABCDEFGHIJ
1YearNameFood/MiscNo.ofTimes20062007
22007PeterFF12MFMF
32006TomFF15PeterF00028
42007AnnMM15TomF01502
52007PeterFF16AnnM190150
62006PeterMM3PeterM30170
72007TomFF2
82006AnnMM1
92007PeterMM17
102006AnnMM18
Sheet2


All the best
PT
 

Forum statistics

Threads
1,137,062
Messages
5,679,392
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top