CODEWITHSUNDEEP
excelexcel-formula
Jyrkki
Jyrkki

Reputation: 43

Count number of variables in row and sum based on defined variable values defined in different table

I have a table with employees, dates and variables. In a different table the variables have assigned values. This is basically a work hours log table.

Basically the two tables look like this.

Table 1

1 A         B  C  D  E  F   G   H         I
2 April     01 02 03 04 ... 30 
3 Employee  AB BC CD EF HI  JK  Employee  Sum-of-hours
4 Employee2 BC BC BC AB AB  BC  Employee2 Sum-of-hours
5 Employee3 BC    AB AB JK      Employee3 Sum-of-hours
...

Table 2

Variable Hours
AB       12
BC       7
CD       8.5
EF       9
HI       5
JK       10
...

The goal is to calculate the sum of hours logged based on the variables marked in Table 1. E.g. I-3 should be AB(12) + BC(7) + CD(8.5) + EF(9) + HI(5) + JK(10) = 51.5 hours.

This approach might not be the most effective, but it's what I've been tasked with solving.

Thanks in advance for any help!

Upvotes: 1

Views: 68

Answers (2)

Jacob Edmond
Jacob Edmond

Reputation: 808

With your Variable column range defined as 'Variables' and the Hours column range defined as 'Hours' you can use this:

{=SUM((Hours)*COUNTIF(2:2,Variables))}

This is an array and must be entered using CTRL+SHIFT+ENTER

If you are looking to have this on the same row and the employee, then you will need to limit the 2:2 range to the specific columns (B2:F2, etc).

enter image description here

enter image description here

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

In a rather clunky way, you can just combine Vlookup() formulas:

=VLOOKUP(B3,[table range],2)+VLOOKUP(C3,[table range],2)+...+VLOOKUP(G3,[table range],2)

For example, enter image description here

Note there's no error handling, and assumes each variable is in the table.

Upvotes: 1

Related Questions