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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
-
No problem at all 🙂 Have a good weekend!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!