How do I total the answers from a drop-down cell?

BethWork

I'm trying to build a dashboard that will show the progress of sites completed for an upcoming upgrade. I have attempted several ways to compile this information automatically and I'm at a loss. I think a simple cell formula might be the easiest way to accomplish what I need then I can link the cell to a report I create.

The cell values are "Not Started" and "Completed". I simply want it to tell me how many cells in the column are at status completed and how many are at status not started.

I have pivot app which I didn't find achieved what I needed as it only let me tally the total number of cells in that column and not the total completed/not started.

Is there a formula that will accomplish what I want?

Andrée Starå

Hi @BethWork

I hope you're well and safe!

Try something like this.

=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Darren Mullen

@BethWork If all the data is in one sheet, that is easy. You have a number of ways to do this.

1. Create a report of the sheet then summarize by the status column. It will give you the count, and you can create a dashboard widget based on this report that will display the counts
2. Use sheet summary fields and the SUMIF formula to count the rows that match your criteria
3. Use a metrics sheet to pull in the data and SUMIF it count the rows that meet your criteria
4. 
   

Darren Mullen

BethWork

@Darren Mullen

I'm attempting option #3 and having the WORST time figuring out the formula.

I need to use a combination of information to achieve what I need.

As an example of what I'm trying to do below: I want to get the total number of Banner Name1 that are Store Open status "Open" and where Install Status is "Not Started". How do I get that formula to work?

I tried this and it did not work for me and came back unparseable.

=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], “Open”, [Install Status]:[Install Status], "Not Started')

Andrée Starå

Hi @BethWork

I hope you're well and safe!

Try something like this.

=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BethWork

@Andrée Starå

It worked that time. I don't see the difference between the formula I tried and the one you gave me though so I'm not sure why it worked this time. Thank you!

Andrée Starå

@BethWork

Excellent!

Happy to help!

I assume you wrote the formula in word or similar, and then the characters might be wrong. (see below)

Your formula (errors in bold)

=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], “Open”, [Install Status]:[Install Status], "Not Started')

My formula (corrected errors in bold)

=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")

BethWork

@Andrée Starå

Yes, you're right that I did write it in Word. I'll use notepad++ next time. Thank you!

Andrée Starå

@BethWork

You're more than welcome!