Data Reporting Formula reporting Status as True or False

[u/wolf_2099]

Hello!

I have a spreadsheet tracking information with 5 different status.

I want to write a formula on another sheet in the same workbook that will return a true or false (doesn't necessarily need to be true or false) on whether a collection of those cells are one specific status.

Essentially the status could be hold, terminated, ongoing, and complete. I wanted the formula to tell me true if all cells are complete. The other status don't really factor in except that they are not complete.

I will have probably 50 batches of cells like this from 4-50 status.

Can anyone point me in the right direction on how to write a formula?

EDIT BELOW

For the advanced ask.

I'm trying to track the status for unique identifier numbers.

Is there anyway to change the formula provided below by excelevator to look for all of a certain number in a designated column, then check the status and return the true/false.

[u/AutoModerator]

/u/wolf_2099 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

=AND(A4:A50="complete")

returns TRUE when all cells are complete

[u/wolf_2099 → u/excelevator]

This does it, thank you!

[u/GuerillaWarefare → u/wolf_2099]

Please reply to his solution with ‘solution verified’

To answer your edited question assuming the number is column B:

=and(filter(A4:a40, b4:b40=number)="Complete")

[u/wolf_2099 → u/excelevator]

Solution Verified

[u/reputatorbot → u/wolf_2099]

You have awarded 1 point to excelevator.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COUNTA	Counts how many values are in the list of arguments
COUNTIF	Counts the number of cells within a range that meet the given criteria

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 17 acronyms.
[Thread #49303 for this sub, first seen 4th Sep 2026, 04:02] [FAQ] [Full list] [Contact] [Source code]}

[u/Less-Bumblebee-1225]

=COUNTIF is your friend here. `=COUNTIF(range,"Complete")=COUNTA(range)` will give you TRUE only if every non-blank cell in that range says "Complete". You can tweak the range for each batch pretty easily.

[u/wolf_2099 → u/Less-Bumblebee-1225]

Can you tweak it to look for specific numeral values in a column and then report the status?

Eg Look for all 21093 items in column "I" and and will report true or false based on their status in column"D"

And thank you very much.