0

I am using the below formula:

=IF(COUNTIFS('New In'!F:F,YTD!$A2,'New In'!B:B,YTD!$P$217)=0,"",COUNTIFS('New In'!F:F,YTD!$A2,'New In'!B:B,YTD!$P$217))

what it needs to do is show a blank if the result is zero - It does work, to an extent - If the result is zero, it shows a blank. However, if the cell value is greater than zero, it shows a circular reference error.

Any ideas whats happened?

David Andrei Ned
  • 799
  • 1
  • 11
  • 28
dmorgan20
  • 353
  • 8
  • 33
  • 1
    Circular reference... The first thing to check is if the formula you are showing is deployed within a cell covered by the ranges of the formula itself. – FDavidov Aug 18 '16 at 09:01

2 Answers2

1

I might be way off here but is the formula in column 'P'?

If this is the case you may be dragging the formula down and overwriting the data in $P$217.

This would cause the circular reference in cell P217.

Wolfie84367
  • 102
  • 1
  • 10
0

Your formula is fine, so you must have it within the cell range you are specifying to do the counting

=IF(COUNTIFS(F5685:F5687,F6069,E5685:E5687,E6069)=0,"",COUNTIFS(F5685:F5687,F6069,E5685:E5687,E6069))

^ works as expected

=IF(COUNTIFS('New In'!F:F,YTD!$A2,'New In'!B:B,YTD!$P$217)=0,"",COUNTIFS('New In'!F:F,YTD!$A2,'New In'!B:B,YTD!$P$217))

^ your formula for comparison

David Andrei Ned
  • 799
  • 1
  • 11
  • 28