COUNTIFS Function | Smartsheet Learning Center
https://help.smartsheet.com/function/countifsCounts the number of times all given conditions in their respective ranges are met.
How to count the number of photo shoots, not completed, by brand? COMPLEX???
Shawn_K2
✭✭✭✭✭
Trying to get the summary of the amount of photo shoots from one column, not checked completed from second column, and sorted by the brand by third column.
So three columns.
(Brand column with specific brand) + (OW Shoot Date column) + (Photo Shoot Complete column) = Total shoots by brand.
Work Smarter, Not Harder
Tags:
Answers
-
Hi @Shawn_K2 , Have you attempted to create a report and specifying the filters that you listed? If not I would try that route first to see if that meets your need.
Within reports, I would recommend looking into Filters, Grouping, and Summarize. These are all features that you can find solid help for within the community.
If you make a little progress, but need some additional help, share a little more about what you've tried. I'm happy to help, but it's hard to describe without knowing what you've been trying.
I hope this is gets you going in the right direction. Be well!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
Hi! I've tried to create a formal: =COUNTIF({Photo Shoot Date}, {Brand}, "Baby Einstein")
Received #INCORRECT ARGUMENT SET error
Work Smarter, Not Harder
-
Hi @Shawn_K2
I hope you're well and safe!
Are you referencing another sheet or is everything in the same sheet where you add the formula?
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Work Smarter, Not Harder
-
You are going to want to start with a COUNTIFS (with the "S" on the end) so that you can include multiple range/criteria sets.
The other thing would be... What is the criteria for the date column? Is it a specific date or date range, or is it just "not blank"?
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Paul Newcome @Andrée Starå The criteria would be photoshoot complete column "not checked" complete.
So I guess would need a formula like:
=COUNTIFS({Photo Shoot Date}, {Brand}, "Baby Einstein", "0")
"0" for unchecked photoshoots?
4 range/criteria sets: Brand, Brand Name, OW Shoot Date, Photoshoot Complete
Final result is to have the total number of photoshoots not completed by Brand.
Work Smarter, Not Harder
-
Try this instead:
=COUNTIFS({Brand Range}, @cell = "Baby Einstein", {Checkbox Column}, @cell <> 1)
-
Prefect! I just change @cell <>1 to @cell <>0 if I want the unchecked items @Paul Newcome
Work Smarter, Not Harder
-
No. @cell <> 1 is counting items that are unchecked. <> means "not equal to" and 1 means "checked. Thus @cell <> 1 means "the cell is not equal to checked".
-
-
-
@Paul Newcome Now what if I want to include items that have dates, but are not completed.
So - Brand, Brand Name, OW Shoot Date, Photoshoot (Not) Complete
=COUNTIFS({Brand Range}, @cell = "Baby Einstein", {OW Shoot Date}, @cell <> 1, {Checkbox Column}, @cell <> 1)
Error - #INVALID REF
Work Smarter, Not Harder
-
That particular error comes from a {Cross Sheet Reference} that wasn't set up properly. Double check that each {Range} has been set up according to the proper steps.
Then your criteria for the date range would be
@cell <> ""
(not equal to blank)
-
Thank you very much. I'm going to try it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …