Combining 2 formulas

[ALI_SV]

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/training1/training2......so if employee is trained we have as comment the "date" of training  or "yes"

on sheet2 I would like to make a count of total trainings each employee had but I just can't put the correct formula which I believe should include COUNTIF and VLOOKUP, besides I have 2 different values (date/text) that I want to count simultaneously on 1 cell and I don't know how

could any one help me please !!! 

[#2]

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.

[#3]

Hi there @mtarler , thanks for your reply,

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

sheet 1

sheet2

[#4]

@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". 

[#5]

@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.