Summing an item in multiple areas

[bobgrand]

Hello all,

I have a spread sheet of inventory history with sales that have the same item in multiple areas with different quantities. In another sheet I have a single list of these items. I would like to get the total sum of an item from sheet 1 to show in sheet 2.

Sheet 1 - Sales Quantities
THHN 8 SOL - 2500
THHN 10 SOL - 3000
THHN 12 SOL - 1500
THHN 10 SOL - 3500
THHN 8 SOL - 2500

Sheet 2 - Totals
THHN 8 SOL - 5000
THHN 10 SOL - 6500
THHN 12 SOL - 1500

Any help would be greatly appreciated

 

[alansidman]

With Power Query

Book3
	A	B	C	D	E
1	Column1	Column2		Column1	Total
2	THHN 8 SOL	2500		THHN 8 SOL	5000
3	THHN 10 SOL	3000		THHN 10 SOL	6500
4	THHN 12 SOL	1500		THHN 12 SOL	1500
5	THHN 10 SOL	3500
6	THHN 8 SOL	2500
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Total", each List.Sum([Column2]), type number}})
in
    #"Grouped Rows"

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMIFS(Sheet1!B:B,Sheet1!A:A,A2)

 

[bobgrand]

Fluff,
Thank you very much. Thats perfect

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bobgrand]

You're welcome & thanks for the feedback.

Hi Fluff,

I am working with the formula you suggested and I am struggling trying to get this to work. Let me point out the details.

In the Data tab Column A is the item. Column B needs to be the over all totals from the Releases Tab. Column A is the item Column B is are the quantities.

Tab - DATA
*MC 4/4 BKRDBLWH 881
*MC 1/4 BKRDBLWH 656
THHN 10 SOL COPPER BARREL 2

Tab - Releases
*MC 4/4 BKRDBLWH 90
*MC 4/4 BKRDBLWH 156
*MC 1/4 BKRDBLWH 156
THHN 10 SOL COPPER BARREL 1
*MC 4/4 BKRDBLWH 210
*MC 4/4 BKRDBLWH 425
*MC 1/4 BKRDBLWH 500
THHN 10 SOL COPPER BARREL 1

Any help would be greatly appreciated.

Bob

 

[Fluff]

In what way isn't it working?

 

[bobgrand]

I am getting all zeros

 

[Fluff]

In that case check the the values in col A are an exact match & that the values in col B are real numbers & not text.

 

[bobgrand]

All values in A are an exact match and the number column is formatted as Number with commas for thousand separation.
There are values showing up against items that should be zero