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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!