Column Reference Formula Returning Incorrect Answer

JHand

I am writing a simple COUNTIFS formula and I am getting a very different answer when I created a column reference vs. referencing a specific range within the column. What am I missing? I am trying to count the number of customers that are not installed or closed to determine how many are "in process".

This formula which references the specific range within a column (in this case row 1 - 167) and it returns the CORRECT answer of 54.

=COUNTIFS({e|tab Master Tracker - INSTALLED Column Rows}, 0, {e|tab Master Tracker - CLOSED Column Rows}, 0)

This formula is referencing the entire column and is returning an incorrect answer of 64.

=COUNTIFS({e|tab INSTALLED Flag}, 0, {e|tab CLOSED Flag}, 0)

I will be adding to the sheet regularly and I don't want to have to update the reference every time new rows are added. With the first formula we run the risk of incorrect data being presented on our dashboard because some customers were inadvertently excluded if the range isn't regularly updated.

Thanks for the help - I've look for the answer here but gave up after 30 minutes...

Genevieve P.

Hi @JHand

Is it possible that there are some blank rows where the flag icon is visible and is being read as un-checked, because the rows are blank? It looks like the count is 10 more, which might indicate it's reading the 10 blank rows at the bottom of your sheet.

Try adding another criteria in there... such as the Primary Column not being blank:

=COUNTIFS({e|tab INSTALLED Flag}, 0, {e|tab CLOSED Flag}, 0, {Primary Column}, <> "")

Genevieve P.

Hi @JHand

Is it possible that there are some blank rows where the flag icon is visible and is being read as un-checked, because the rows are blank? It looks like the count is 10 more, which might indicate it's reading the 10 blank rows at the bottom of your sheet.

Try adding another criteria in there... such as the Primary Column not being blank:

=COUNTIFS({e|tab INSTALLED Flag}, 0, {e|tab CLOSED Flag}, 0, {Primary Column}, <> "")

JHand

This worked perfectly! Thank you so much for the assistance.

Genevieve P.

Great! Happy to help 🙂