IF/Then function or Index/Match function...unsure of best route

Austin Burton · 05/12/21

I need to create a function where we have a lead with assistants working under them, and are needing to calculate the total of their office size. If the assistant(s) are a SHARED resource, their value is 1, regardless of how many shared assistants are dedicated to that lead. If their assistant(s) are dedicated to their office, then each of their values is one and will need to be added together.

For Example if a lead has 4 shared resources and 4 dedicated resources, then the total count should be 5. 1 for the shared resource as a group, and 1 each for the 4 dedicated resources.

Currently I have columns for each assistants name, so if a lead has 4 dedicated assistants there are four columns DA1, DA2, DA3, & DA4. Same for the shared resources. SA1, SA2, SA3, SA4. I also have a checkbox if the leader has one of the assistant types on their team, figured that would be easier for the shared side of things so I could just return a simple true, false to decide if they get a count for that assistant.

I am here:

=IF[SA]1="True", "1",=IF[DA]1="True", "=SUM [DA 1, DA 4]

I apologize, this function is extremely tricky to me and I am a bit lost.

Heather Duff · 05/12/21

Hi @Austin Burton,

Will the person always have a shared resource? If they don't, will they still have a 1 count? If so, you could do this:

=1+COUNTIF([DA 1]@row:[DA 4]@row,<>"")

This reads "1 plus the count of how many cells in DA1 through DA4 are not blank.

If someone without any SAs will have a value of 0 before adding in DAs, then you could use:

=IF(COUNTIF([SA 1]@row:[SA 4]@row,<>"")>0,1,0)+COUNTIF([DA 1]@row:[DA 4]@row,<>"")

Let me know if it works for you!

Best,

Heather

Paul Newcome · 05/12/21

Are you able to provide a screenshot for reference?

Heather Duff · 05/12/21