# Workaround For Circular Refernce Using COUNT

#### Bingo969

##### Board Regular
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:
Code:
``=COUNTIF(\$B\$3:\$N\$5,N3)``
Q3:
Code:
``=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
ABCDEFGHIJKLMNOPQ
1TOPCHARGES-APRIL
2Supplies#MTD#YTDPhone#MTD#YTDExpense#MTD#YTD
3123Bob\$1022123Bob\$5022700Tina\$4425
4456Stan\$8215412Jack\$3211852Lyle\$2811
5789Lyn\$311700Tina\$2525456Stan\$26215
Sheet4

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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))

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.

Replies
12
Views
218
Replies
4
Views
2K
Replies
1
Views
348
Replies
1
Views
357
Replies
1
Views
394

1,203,483
Messages
6,055,679
Members
444,807
Latest member
RustyExcel

### 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?

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