CODEWITHSUNDEEP
excelsumexcel-2010conditional-statementsarray-formulas
GPH
GPH

Reputation: 1897

Lookup total based on a lookup table SUMIF

I need to do a total sum based on a look up, however the look up is dynamic. To be more specific I have a totals table like so:

Type    | Total
Charges | 633.86
Costs   |
Misc    |

This needs to sum up figures based on certain code in a look up table:

Type    | Code
Charges | N77RE
Charges | N5762
Charges | M7YRE
Costs   | M888
Costs   | O999
MISC    | ABCS12
MISC    | ABCs13
MISC    | ABCS14

The financial data is presented like so:

Transaction | Code  | Costs
123456      | N77RE | 19.99
123454      | O999  | 24.99
123452      | N5762 | 458.99
123450      | O999  | 487.55
123448      | N5762 | 45.55

So to sum it up I need to do a look up function to check to see what code has what value and then sum it up?

This image is based on the answer, it only seems to sum up the first Code?

enter image description here

Upvotes: 0

Views: 751

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Assuming your second table (Types and Codes) is in D1:E9 and the third table (Transactions, Codes and Costs) is in G1:I6 then with "Charges" in A2 use this "array formula" in B2

=SUM(SUMIF(H$2:H$6,IF(D$2:D$9=A2,E$2:E$9),I$2:I$6))

confirm with CTRL+SHIFT+ENTER

and copy down as required

The IF function provides a list of the valid codes for the type in A2 then SUMIF sums the costs for each code. This gives you an array of totals which SUM then sums to give your final answer.

For the example shown I'd expect to get 524.53 as the total for Charges

Upvotes: 1

Related Questions