Reference Cell Deleted - Avoiding #REF!

[ERI GURU]

Hello,

I have an Excel sheet (SheetA) that refers to another to collect data (SheetB). SheetB is used for presenting the initial data, and SheetA is hidden and used solely as a bridge for charts on other sheets. Because SheetB is used in presentations, it must be kept neat, and any cells not in use must be deleted.

Long story short, is there any formula that allows the cells on SheetA to return a value equal to 0 if one or all of the reference cells are deleted from SheetB?

I am just looking for a general formula that I can add to my existing.

Example:

'SheetB'!A1=P
'SheetB'!B1=E

'SheetA'!A1=COUNTIFS('SheetB'!$A1:$A4,"P",'SheetB'!$B1:$B4,"E")

The resulting count would be 1

If I were to delete rows 1-4 from SheetB, I need the count to equal 0, not #REF!

Thank you in advance

[popipipo]

Use 'iferror'
=iferror(COUNTIFS('SheetB'!$A1:$A4,"P",'SheetB'!$B1:$B4,"E"),"")

[Ottrree]

I agree. But if you want a result of 0 instead of a blank cell:

=IFERROR(SheetA!A1=COUNTIFS(SheetB!$A1:$A4,"P",SheetB!$B1:$B4,"E"),0)

[ERI GURU]

This works perfect, Thank you!!

[Ottrree]

Yay! Glad to help.

[gmr4evr1]

Oddly enough, when I copied and pasted the formula =COUNTIFS('SheetB'!$A1:$A4,"P",'SheetB'!$B1:$B4,"E") and deleted all of the "P"'s or "E"'s, it returned a value of 0 in the cell SheetA A:1. Does the cell in your worksheet have some sort of formatting applied to it?

Never mind, already resolved

[Ottrree]

IFERROR only takes effect if there is an error message like #N/A.

If I am understanding the above post, you asked the formula in Sheet B to count occurrences, then deleted all the occurrences. So COUNTIFS returned a count of 0.

[gmr4evr1]

Not all occurrences, either all the "P"'s or all the "E"'s. I didn't try deleting all of both. But yes, it did return a count of 0. There is a good chance I did something wrong and still achieved the result he wanted lol

[FDibbins]

Because SheetB is used in presentations, it must be kept neat, and any cells not in use must be deleted.

I get that, but have you considered hiding those rows, instead of deleting them? As you have discovered, deleting cells (I assume you mean really deleting the cell/row, as opposed to deleting the contents?), causes all sorts of havoc with formulas