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.

Nic Larsen

You need something like this / just update the name and the Tally type: 

=COUNTIFS({LEAD SME}, =SME@row, {Tally Range}, =1, {Tally Type}, ="TTX") + COUNTIFS({SME 2}, =SME@row, {Tally Range}, =1, {Tally Type}, ="TTX") + COUNTIFS({SME 3}, =SME@row, {Tally Range}, =1, {Tally Type}, ="TTX")

There is likely a way to make this easier, but this was working for me on a similar formula/case I use.  

 

L_123

It can be done, I really don't think it is easier than Nic's formula

=Count(COLLECT(ID:ID, [Tally Type]:[Tally Type], @cell = "FTX", ID:ID, OR(INDEX([Lead SME]:[Lead SME], MATCH(@cell, ID:ID, 0)) = SME1, INDEX([SME 2]:[SME 2], MATCH(@cell, ID:ID, 0)) = SME1, INDEX([SME 3]:[SME 3], MATCH(@cell, ID:ID, 0)) = SME1)))

 

Where the Column "ID" is an autonumber column. It doesn't matter what type you choose.

Shesha_K

This worked perfectly! Thank you so much for your help 

 

Paul Newcome

Happy to help! 

 

I use this setup regularly for tracking individual stats in group projects.

Shesha_K

Hi Paul,

With the wild success of automatically calculating the number of event types per individual, it was asked if we could do the same for the country/SME interactions. They would like a tally of which SME's have worked an event with which countries. Ultimately, I thought I could just tweak the original formula, but it is apparent that I need a little more help. So, I created a column that lists the countries that participated in the particular event, and hoped to use the "joined" column to reference the individuals again keeping with the check in the Tally or Not column. I have tried:

=COUNTIFS({Past Events Range 2}, FIND(FTX$17) > 0, {Past Events Range 1}, FIND(SME18) > 0, {Past Events Range 4}, 1)

=COUNTIFS({Past Events Range 2}, FIND(FTX$17) > 0, {Past Events Range 1}, SME18, {Past Events Range 4}, 1)

Any help with this is greatly appreciated!