Combining SUMIFS with "VLOOKUP alternative" which should return multiple matches

[Davidovic]

Hi,

I have a log containing time entries from employees. This log does not contain their position (Developer, Project Manager, QA Engineer).
However, I need to provide a report based on the positions. There are multiple Developers and multiple QA Engineers. What I am trying to get in cell I3 is ALL billable hours for ALL Developers.

In the screenshot you can see an example of this setup and what I'm trying to do:

On the left is the log containing Employee's names and logged hours. Then I have a lookup table to get the Position matching the Employee's name. On the right I have my report where I'm trying to do something like this:

=SUMIFS(log_hours;log_billable;1;VLOOKUP(log_employee;lookup_employee_position;2;0);H3)

• log_hours are the hours to be summed
• log_billable must be 1, because I only want the billable hours
• log_employee contains the employee's name. Here is where I'm stuck. I use the employee's name to lookup the position, and that needs to match the position in the report. In the popup window, it actually looks OK, because both the "Criteria_range2" and the "Criteria2" fields show "Developer", so that should be a match. However, when I press "OK", I get the following error:
  

What am I doing wrong? Can this work anyway? If not, what would be an alternative solution/formula?

Any help is appreciated!

 

[Davidovic]

Here is the table, copy-pasted, if anyone wants to give it a shot

log				lookup_employee_position			report
employee	hours	billable		employee	position		logged and billable hours per position
Adam	5,00​	1​		Adam	Developer		Developer	#VALUE!​
Bob	2,00​	1​		Bob	Project Manager		Project Manager
Claire	6,00​	1​		Claire	QA Engineer		QA Engineer
Dave	4,00​	0​		Dave	Developer
Eric	3,00​	1​		Eric	QA Engineer
Adam	3,00​	1​
Bob	6,00​	0​
Claire	3,00​	1​
Dave	5,00​	1​
Adam	4,00​	1​
Eric	2,00​	0​
Bob	3,00​	1​
Claire	4,00​	0​
Eric	6,00​	1​
Dave	2,00​	1​

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	log				lookup_employee_position			report
2	employee	hours	billable		employee	position		logged and billable hours per position
3	Adam	5	1		Adam	Developer		Developer	19
4	Bob	2	1		Bob	Project Manager		Project Manager	5
5	Claire	6	1		Claire	QA Engineer		QA Engineer	18
6	Dave	4	0		Dave	Developer
7	Eric	3	1		Eric	QA Engineer
8	Adam	3	1
9	Bob	6	0
10	Claire	3	1
11	Dave	5	1
12	Adam	4	1
13	Eric	2	0
14	Bob	3	1
15	Claire	4	0
16	Eric	6	1
17	Dave	2	1
Data

Cell Formulas
Range	Formula
I3:I5	I3	=SUM(FILTER($B$3:$B$17,(ISNUMBER(MATCH($A$3:$A$17,FILTER($E$3:$E$7,$F$3:$F$7=H3),0)))*($C$3:$C$17=1)))

 

[Davidovic]

Thanks @Fluff ! The results in your example are exactly what they should be, so that's great. However, when I enter the formula in my file, I still get the error I showed in my initial post.

Also, how would I add additional criteria in your formula? For the sake of simplicity, I omitted the Date here, as I was trying to deal with the Position first. In the actual report, I'd also need to group by month. I know how to do this using SUMIFS (then I'd just compare the date of the entry with the start/end of the months as an additional criteria. In your Formula, I'm having a hard time understanding how it works and therefore how to adapt.

log					lookup_employee_position			report
employee	hours	date	billable		employee	position		logged and billable hours per position	Jan	Feb	Mar
Adam	5,00​	01.01.2020​	1​		Adam	Developer		Developer
Bob	2,00​	05.02.2020​	1​		Bob	Project Manager		Project Manager
Claire	6,00​	09.03.2020​	1​		Claire	QA Engineer		QA Engineer
Dave	4,00​	27.01.2020​	0​		Dave	Developer
Eric	3,00​	13.03.2020​	1​		Eric	QA Engineer
Adam	3,00​	26.02.2020​	1​
Bob	6,00​	08.01.2020​	0​
Claire	3,00​	01.01.2020​	1​
Dave	5,00​	05.02.2020​	1​
Adam	4,00​	09.03.2020​	1​
Eric	2,00​	27.01.2020​	0​
Bob	3,00​	13.03.2020​	1​
Claire	4,00​	26.02.2020​	0​
Eric	6,00​	08.01.2020​	1​
Dave	2,00​	13.03.2020​	1​

Thanks again!

 

[Fluff]

Lets get the first formula working, before anything else.
Do you have any #VALUE! errors in your data set?

 

[Davidovic]

Yeah you're right, first things first.

I'm not getting #VALUE! errors I believe. Since I can't upload files here, here's a share link via wetransfer.com: excel test.xlsx

I hope you're OK with having a look at it.

 

[Fluff]

If you're not getting a #value error, what error are you getting?

 

[jasonb75]

See if this alternative formula works, or causes the same problems. Must be array confirmed with Ctrl Shift Enter, otherwise the results will be wrong (42 instead of 19 for the first row).

=SUMPRODUCT(SUMIFS(log_hours;log_billable;1;log_employee;IF($F$3:$F$7=H3;$E$3:$E$7)))

 

[Peter_SSs]

However, when I enter the formula in my file, I still get the error I showed in my initial post.

Are you remembering to substitute ";" formula separators instead of the "," separators in Fluff's formula?

 

[Davidovic]

Are you remembering to substitute ";" formula separators instead of the "," separators in Fluff's formula?

Hi @Peter_SSs thanks! I now took @Fluff 's formula and replaced all , with ; and it works! But what's that about? Different Excel versions or something?