Countif but only if?

Shesha_K

So I am trying to count the number of times a person worked on a specific task.  I have various "types", each person might have worked on the task in a different role (Lead SME, SME 2, SME 3), and then I have a check mark whether or not it should be included in the "tally".  Ultimately, what I am trying to do would be if the tally column is checked, and the persons name shows up in any of the SME columns, to count the number of TTX's for instance.  So if Barnes worked in any capacity on a TTX and the tally check is checked, what is the total number of TTX's? And then repeat that process for the rest of the SME's and types of events.

Paul Newcome

I would suggest a helper column where you join the 3 SME columns together. FOr this example I'll Call it SMEH (for SME Helper). Enter

 

=JOIN(COLLECT([Lead SME]@row:[SME 3]@row], [Lead SME]@row:[SME 3]@row], ISTEXT(@cell)), " - ")

.

I would then suggest a helper row on your metrics sheet. For this example I will use row 1 and have the metrics starting on row 2.

The table would look something like this (column headers in bold)...

SME                  FTX          TTX          Drills/LSO

                          FTX          TTX          Drills/LSO

Barnes

Bartnowack

Cole

.

In FTX2 you would use the formula

=COUNTIFS({Master Sheet Range SMEH}, FIND($SME@row, @cell) > 0, {Master Sheet Range Tally Type}, FTX$1, {Master Sheet Range Tally or Not}, 1)

.

NOTE: The $'s used to lock row and column references. This is to facilitate dragfilling both across the rows and down the columns once the formula has been established in the top right corner of the table (FTX2).

.

{Master Sheet Range SMEH}: Use appropriate steps for referencing another sheet and select the SMEH column.

{Master Sheet Range Tally Type}: Same as above except selecting the Tally Type column.

{Master Sheet Range Tally or Not}: Same as above except selecting the Tally or No column.

@row: Leave these references as is.

.

Enter as many names as you need in the SME column and finish the table out across row 1 for whatever types of tallies you have. You can then use dragfill to fill in the rest of the table and the formula will autopopulate with the correct row and column references. This keeps you from having to manually update every cell with the correct row and column references for the different names and tally types.