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!

 

[Fluff]

The separator varies depending on location.
To include the date try

excel test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	log					lookup_employee_position			report
2	employee	hours	date	billable		employee	position		logged and billable hours per position	Jan	Feb	Mar
3	Adam	5.00	01/01/2020	1		Adam	Developer		Developer	5	8	6
4	Bob	2.00	05/02/2020	1		Bob	Project Manager		Project Manager	0	2	3
5	Claire	6.00	09/03/2020	1		Claire	QA Engineer		QA Engineer	9	0	9
6	Dave	4.00	27/01/2020	0		Dave	Developer
7	Eric	3.00	13/03/2020	1		Eric	QA Engineer
8	Adam	3.00	26/02/2020	1
9	Bob	6.00	08/01/2020	0
10	Claire	3.00	01/01/2020	1
11	Dave	5.00	05/02/2020	1
12	Adam	4.00	09/03/2020	1
13	Eric	2.00	27/01/2020	0
14	Bob	3.00	13/03/2020	1
15	Claire	4.00	26/02/2020	0
16	Eric	6.00	08/01/2020	1
17	Dave	2.00	13/03/2020	1
Sheet1 with DATE

Cell Formulas
Range	Formula
J3:L5	J3	=SUM(FILTER($B$3:$B$17,(ISNUMBER(MATCH($A$3:$A$17,FILTER($F$3:$F$7,$G$3:$G$7=$I3),0)))*($D$3:$D$17=1)*(TEXT($C$3:$C$17,"mmm")=J$2),0))

Once again changing the commas to semi colons.
If you are using a non-english version there is an excellent site here Translator that you can use to convert formulas to your own language.

 

[Peter_SSs]

But what's that about? Different Excel versions or something?

Yes, it depends on your regional language settings. More information here

 

[Davidovic]

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

Thanks @jasonb75! While I did get the other suggested formula working after replacing the ',' with ';' , I actually prefer this one, because with the SUMIFS it's easy for me to add additional criteria for date ranges.

Helped me a lot!