CODEWITHSUNDEEP
excelexcel-formula
Per Larsen
Per Larsen

Reputation: 1189

Excel formula to match cells from one table to cells from another table and return summed values

I have the following set of example data. The actual data will have up to 24 rows per code and 50 codes. I need to do a few things.

  1. Match data from table A to data in table B on Code
  2. Where the code matches I need to sum the values in table A Hours column if any of the cells match the value in table B's day column
  3. Put the summed value from #2 into the Compare column in table B where the day cells match form table A to B

enter image description here

Example: In table A Code=A00R and Day=2 I have two values for Hours, 4.47(Row 3) and 3.83(Row 4)

In table B Code=A00R and Day=2 I need to put the SUM of 4.47 and 3.83 or 8.3 enter image description here

I have tried using Match and Index. Which I have used many times before to match up data from different tables, but these have always been a one for one match.

Any assistance is greatly appreciated.

Upvotes: 1

Views: 718

Answers (1)

Ass3mbler
Ass3mbler

Reputation: 3915

You should use the SUMIFS() function.

In your example:

=SUMIFS(C1:C34;A1:A34;E1;B1:B34;F1)

With this function, you can specify more than one criteria to filter the sum operation. For each criterium specified, you can also indicate the range. So, in your example, the first parameter (C1:C34) is the range used to perform the sum, the second parameter (A1:A34) is the range used to evaluate the first test and the third parameter (E1)is the comparison cell for the test. So we are telling the function to check before summing, as first step, if the values in the range (A1:A34) are equal to the cell (E1). After that, the fourth and the fifth parameters specify another range and another test. In this case, we are telling the function to check, before summing, if the values in the range (B1:B34) are equals to the cell (F1). Both the condition should be met at the same time (the function evaluates all the criteria specified using a boolean AND operator).

Upvotes: 2

Related Questions