Formula for countifs and or from multiple columns

aaddison

Hello,

I am trying to count if {Goal #1} and {Goal #2} are not blank IF {Office Name} is [Primary Column]@row OR {Division Name] is [Primary Column]@row.

The way my tracker sheet is set up is that I have Office Name & Division Name as separate columns and I need to count that each office or division has completed Goal #1 and Goal #2 (anything can be in the field.)

I'd like to use one formula for the whole metrics page for ease, so below is what I have now and it seems to be working, but I'm curious if there's a simpler way to either set up my tracker or write the formula.

=SUM((COUNTIFS({Division Name}, [Primary Column]@row, {Goal #1}, <>"")) + (COUNTIFS({Division Name}, [Primary Column]@row, {Goal #2}, <>"")) + (COUNTIFS({Office Name}, [Primary Column]@row, {Goal #1}, <>"")) + (COUNTIFS({Office Name}, [Primary Column]@row, {Goal #2}, <>"")))

Thanks!

KPH

Your current formula is adding the number of rows where

the division name matches the primary column and goal one has a value

plus the number where the division matches and goal two has a value

plus the number where the office matches and goal one has a value

plus the number where the office matches has goal two has a value.

It is not checking that the division or office has both goal 1 AND goal 2. So depending on your data might, it not be working. If both goals are specified your total will be 2. If only 1 is specified the total will be 1. If the same division or office has a second row with the same goal in, the total will also be 2.

An alternative, and simpler, formula is.

=(COUNTIFS({Division Name}, [Primary Column]@row, {Goal #1}, <>"", {Goal #2}, <>""))

+ (COUNTIFS({Office Name}, [Primary Column]@row, {Goal #1}, <>"", {Goal #2}, <>""))

Just be aware, it is not doing exactly what your formula is. This one is counting only the rows where the division name matches the value in the primary column and goal 1 is not blank and goal 2 is not blank. All three things must be true to return a one. It then does the same for Office Name and adds those two counts together.

Hope that helps.

KPH

Your current formula is adding the number of rows where

the division name matches the primary column and goal one has a value

plus the number where the division matches and goal two has a value

plus the number where the office matches and goal one has a value

plus the number where the office matches has goal two has a value.

It is not checking that the division or office has both goal 1 AND goal 2. So depending on your data might, it not be working. If both goals are specified your total will be 2. If only 1 is specified the total will be 1. If the same division or office has a second row with the same goal in, the total will also be 2.

An alternative, and simpler, formula is.

=(COUNTIFS({Division Name}, [Primary Column]@row, {Goal #1}, <>"", {Goal #2}, <>""))

+ (COUNTIFS({Office Name}, [Primary Column]@row, {Goal #1}, <>"", {Goal #2}, <>""))

Just be aware, it is not doing exactly what your formula is. This one is counting only the rows where the division name matches the value in the primary column and goal 1 is not blank and goal 2 is not blank. All three things must be true to return a one. It then does the same for Office Name and adds those two counts together.

Hope that helps.

aaddison

Thank you! This is helpful. Used this formula with symbols so we can quickly see who is "complete" :)

KPH

Wonderful! Thanks for letting me know.