How to Count the Number of Times a Row has a Specific Combination of Values

monica16145

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!

Genevieve P.

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

monica16145

Hey @Genevieve P.

This works perfectly! You're the best.

Thanks,

Monica

Genevieve P.

No problem at all 🙂 Have a good weekend!