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

Formulas and Functions

4 Replies

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