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