CODEWITHSUNDEEP
excel
Mirza Sakibul Hoque Himel
Mirza Sakibul Hoque Himel

Reputation: 3

Match between row and column from two sheets, if found copy value from the third and if more than one under same category sum up the values

On “Sheet 1” I have four columns (A, B, C, D), where “row 1” indicates four categories (transport, food, entertainment, others).

On “Sheet 2” I have 3 columns (A, B, C) and 10 rows (1-10), where on “Column A” I have a list of different types of expenses that I made, on column B I have a drop down list based on categories (Sheet 1; row 1) and on “Column C” I have the price.

I want, when a category is selected from drop down list on “Sheet 2; B1”, the amount from “Sheet 2; C1 (amount that are in same row)” should automatically goes to the "Sheet 1; A2/B2/C2/D2 (depends on selected category)”. If more than one amount is found under the same category then it will sum up the amount.

I have tried LOOKUP and MATCH functions but couldn't do it.

=INDEX(Sheet2!B2:C12,MATCH(Sheet1!B1,Sheet2!B2:B12,0))

Is it possible?

Upvotes: 0

Views: 111

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

On your Sheet1, cell A2 use this SUMIF formula and copy right over to D2:

=SUMIF(Sheet2!$B:$B,A$1,Sheet2!$C:$C)

Upvotes: 1

Related Questions