Forum Discussion

ALI_SV's avatar
ALI_SV
Copper Contributor
Dec 22, 2021

Combining 2 formulas

HI friends,   I've been struggling for a weak with this 😞 help me please,   I've got a list of employee IDs on sheet1 gathering all information on their trainings, for each line I have ID/name/t...
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Dec 22, 2021
without a sample or more details it is hard to know your specific case. For example you are right the first approach would be a count or countif(s). You say you want total count of trainings for a given employee, but wouldn't that just be a count for the entire row - 2 (subtract the name and ID cells) or just count starting on column C assuming ID/Name are columns A and B?
you see it all depends on how your training file is set up.
  • ALI_SV's avatar
    ALI_SV
    Copper Contributor
    Dec 22, 2021

    Hi there mtarler , thanks for your reply,

     

    bellow is the sample you're asking for, hope it's clear enough

     

    sheet 1

     

     

    sheet2

     

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Dec 22, 2021

      ALI_SV  So as mentioned if those "-" are indicative of BLANK cells then a simple COUNTA can be used but if they are in fact a "-" and you want to exclude "-", blanks, zeros, etc.. and you are using at least Excel 2019, then you can get a little more tricky and use a SWITCH command.  The EASIEST thing would be to create a column in the same table and do the sum right there and then the 'remote' table could use a LookUp to find that value.  If you can NOT add the column directly to that table then if you are using 365 you can use a FILTER but if not you will probably need an OFFSET(... MATCH()) combo.  I have attached some examples.

    • Spyclown's avatar
      Spyclown
      Copper Contributor
      Dec 22, 2021

      ALI_SV 

       

      Perhaps If you leave the cells blank for the uncompleted trainings you can run a very simple formula "=COUNTA(CellRange)". I.e. =COUNTA(D3:F3) and will return a value of 3. 

       

      Or you can run the count command to only count cells with a numeric value. The data will need to be a bit more consistent, such as having a date listed versus "yes".