CODEWITHSUNDEEP
excelexcel-formulaformulaspreadsheet
Mr Chasi
Mr Chasi

Reputation: 437

Formula To Find Total For Individual Items Excel

I am trying to create a simple program in which I have 2 tables. Table A lists all the relevant items. Table B is for placing Client requests. As you can see, the Requested Items column on Table B contains a drop-down list with all the predefined items in Table A. There is also a column stating the quantity request for each item.

enter image description here

I am thus trying to create some sort of dynamic formula, which will be able to find all occurrences of a particular item in Table B, whilst calculating the sum of the Quantity for each of these occurrences, and put that value in the "Quantity Requested column in Table A. A simulation with manually-input data can be seen below:

*Table A* with sorted data

Thanks in advance for your responses.

Upvotes: 1

Views: 2078

Answers (2)

A.S.H
A.S.H

Reputation: 29332

Use this structured formula at the column Qty Requested of TabelA (in any cell).

=SUMIFS(TableB[Qty],TableB[Requested Item], [@Item])

This structured format exploits very well the tables; it automatically fills the column and it will adjust whenever any of the two tables grows, shrinks, changes location, etc...

Upvotes: 2

yass
yass

Reputation: 869

Use Sumif in C8 write:
=SUMIF(G:G,B8,H:H)
G:G is the column of Requested Item in Table B
H:H is the column of Qty in Table B
B8 is the Item in Table A
You can drag the formula down

Upvotes: 2

Related Questions