CODEWITHSUNDEEP
excel
Declan Greally
Declan Greally

Reputation: 1083

I'm looking for an alternative to SUMIFS for text using tables

I currently have an issue wherein I can't return text from a table. In order to return something like date of birth, I have no issues as it is a number, the formula I use for that is as follows.

=SUMIFS(tblEmployees[Date of Birth],tblEmployees[List of Employees],tblLeave[[#This Row],[Employee Name]])

This is returning numbers perfectly but is useless for text, I've tried using arrays but I could have been using it wrong. In an ideal world where SUMIFS returns text, my formula would have been as follows.

=SUMIFS(tblEmployees[Department],tblEmployees[List of Employees],tblLeave[[#This Row],[Employee Name]])

tblEmployees and tblLeave are on different sheets as well but the data is being called into the same sheet as tblLeave.

Any help would be great.

I would like to return text from the column Department within tblEmployees, identifying the row using the name of the employee using the employee name. The identifying of the row and column is working, as shown by it returning numbers but I need a function that will allow me to return text in the same manner as SUMIFS returns numbers.

Upvotes: 0

Views: 10222

Answers (1)

pnuts
pnuts

Reputation: 59475

I am not certain why a relatively simple: 

=tblEmployees[[#This Row],[Department]]

did not suit in this case, so mention that in case suitable for someone else, but it seems: 

=INDEX(tblEmployees[Department],MATCH(tblLeave[[#This Row],[Employee name]],tblEmployees[List of Employees],0))

serves the purpose.

Upvotes: 1

Related Questions