Workaround For Circular Refernce Using COUNT

[Bingo969]

Good Afternoon -

I'm using Excel 2007 and XP SP3.

I have a workbook that contains multiple worksheets. These are monthly Top 10 reports of various expenses. They show the top 10 people in various catagories. The format stays the same month to month but the names often may change.

To track repeat occurances (In other words, people consistantly generating charges) I wanted to count 2 things:
1) The number of times a name appears on one sheet (# Month to Date)
2) The number of times a name appears on all sheets (# Year to Date)

While my actual sheet is much larger, this sample shows what I'm referring to. For the very last columns - P and Q - the following formula works:
P3:

=COUNTIF($B$3:$N$5,N3)

Q3:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2}&"!$B$3:$N$5"),N3))

However, trying to use either of those formulas in columns D, E, J or K will result in circular reference errors since they are inside the sample range.

I even tried running those formulas in cells outside the range (Such as using cell S3 to get the result that should go in D3) and then just putting something like =S3 in cell D3 and still get the error.

So does anyone know of a way around that? Some way that I could get just the number of occurances in columns D, 3, J and K?

Thank you,

Matt

Verizon Top Charges.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	TOPCHARGES-APRIL
2	Supplies			#MTD	#YTD		Phone			#MTD	#YTD		Expense			#MTD	#YTD
3	123	Bob	$10	2	2		123	Bob	$50	2	2		700	Tina	$44	2	5
4	456	Stan	$8	2	15		412	Jack	$32	1	1		852	Lyle	$28	1	1
5	789	Lyn	$3	1	1		700	Tina	$25	2	5		456	Stan	$26	2	15
Sheet4

 

[pbornemeier]

Should the formulas in column P&Q only check column N; J&K check H and D&E check B? Then perhaps column R would hold a list of all employees and S&T their monthly and yearly counts.

Alternately, adjust the formulas to only count columns B, H & N

P3=COUNTIF($B$3:$B$5,N3)+COUNTIF($H$3:$H$5,N3)+COUNTIF($N$3:$N$5,N3)
Q3=SUM(COUNTIF(INDIRECT("Sheet"&{1,2}&"!$B$3:$B$5"),N3),COUNTIF(INDIRECT("Sheet"&{1,2}&"!$H$3:$H$5"),N3),COUNTIF(INDIRECT("Sheet"&{1,2}&"!$N$3:$N$5"),N3))

 

[Bingo969]

Thank you!! You are fantastic. I used your second option which is actually something I tried first but couldn't get the arguments correct. Works perfectly the way you did it. Thank you very much.