Combining 2 formulas

Copper Contributor

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 !!! 

4 Replies
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.

Hi there @mtarler , thanks for your reply,

 

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

 

sheet 1

 

ALI_SV_2-1640201035697.png

 

sheet2

ALI_SV_3-1640201091989.png

 

 

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

 

 

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