How to Count the Number of Times a Row has a Specific Combination of Values
I am having a hard time counting the number of times a combination happens in a single row in my sheet - There are formulas that affect one another, making it difficult for me to figure out. Here's some background...
My sheet is tracking photos I need taken by a photographer. Each singular row represents an image (the product being photographed, the angle, the cost, etc.). When the "completion" box is checked in a row, the "Price" drops to $0 (aka, I don't need the image). If the box is unchecked, then the photo is $47.50 and this formula sums the prices: =IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, 47.5), SUM(CHILDREN()))
I have a second column, "Price (Angle A Only)", that calculates price but ONLY for rows that contain "Angle A" in the "Position" column. Essentially, this is an option to only buy the "Angle A" versus both "Angle A" and "Angle B" (making the price significantly cheaper). I have a formula that successfully captures this price: =SUMIF(CHILDREN(Position@row), HAS(@cell, "Angle A"), CHILDREN(Price@row))
The part I am having trouble with is determining how many total photos I will need for the "Price (Angle A Only)" column. I was able to figure out the total number of photos for the "Price" column (864) and it adjusts correctly when boxes are checked/unchecked by using this formula: =COUNTIF(CHILDREN(Completion:Completion), 0)
The route I was taking (that I was unsuccessful with) was to count children rows that have BOTH the "Completion" box unchecked and have "Angle A" in the "Position" column.
I hope someone can help. let me know if I can supply more detail.
Thanks!
Answers
-
Hi @monica16145
Great to see you post again! I'm excited to see how your sheet is shaping up.
You're absolutely on the right track with the COUNTIF and CHILDREN, we just need to turn it into a COUNTIFS plural, to identify your two criteria.
=COUNTIFS(CHILDREN(Completion:Completion), 0, CHILDREN(Position:Position), HAS(@cell, "Angle A"))
Let me know if this worked.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
No problem at all 🙂 Have a good weekend!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!