Combinations of seperate checkbox in rows activating a single count

Brennan Lott

Hello team!

I am trying to categorize 4 different types of work and then also capture how much of each type of work we are approved for. I have four seperate columns of check boxes for different types of work and then a fifth for if the work is approved. the rows have the specific data for the specific work. How can I compare Column A (type A work) as true AND Column I (approved checkbox) as true to create one "count" in my analysis cell? Example: Work type A gets quoted and check box is selected as true (data captured) then work is awarded and awarded checkbox is selected. We now have data on how many of that type of opportunities we have, and how many were awarded to us.

Thanks ahead of time for your help!

Paul Newcome

You are going to want to use something along the lines of...

=COUNTIFS(Construction:Construction, 1, Awarded:Awarded, 1)

Paul Newcome

Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with mock data?

Brennan Lott

Thanks for the quick reply. Here is what I am trying to achieve. Green on the right is to be the data capture point.

Alexander Ford

Hi Brennan,

I like using a nest nested IF+OR for this type of requirement. Let me know if this works for you - these are the same column named you used for your example checkboxes.

=IF(OR(Constr@row = 1, Mod@row = 1, Service@row = 1), 1, 0)

Are you comfortable with using COUNTIFS /SUMIFS for putting together your metrics? Hope that helps!

Paul Newcome

You are going to want to use something along the lines of...

=COUNTIFS(Construction:Construction, 1, Awarded:Awarded, 1)

Brennan Lott

Thanks team. I ended up using a similar function your advice was very valuable.

I have one additional question. I am now hoping to be able to auto sum the awarded work in each category. I am using the following formula but it is not working. I am likely just missing something small, but would love your help. Thanks!

=sumifs((C3:C101, true, I3:I101, true)*j3:j101)

Paul Newcome

You would use a SUMIFS very similar to the COUNTIFS.

=SUMIFS([Awarded Value]:[Awarded Value], Construction:Construction, 1, Awarded:Awarded, 1)