MS Excel - Help with Formula

SHL · Jun 4, 2021

Hi. Need help with a formula for the last column that sums total quantity sold per product per store. I want to enter the formula in D3 and just drag/copy it down.

Store	Product	Qty Sold	Which formula can help sum as below?

B11	104046350	1	2
B11	104046350	1	2
B11	104049971	1	3
B11	104049971	1	3
B11	104049971	1	3
B11	104046260	1	2
B11	104046260	1	2
B11	104045651	1	3
B11	104045651	1	3
B11	104045651	1	3
C12	108017391	1	1
C12	104046112	1	1
C12	104049971	1	2
C12	104049971	1	2
C12	104046260	1	2
C12	104046260	1	2
C12	104046165	1	2
C12	104046165	1	2
E17	108017391	1	1
E17	104046350	1	1
E17	104049971	1	1
E17	104046093	1	3
E17	104046093	1	3
E17	104046093	1	3
E17	104050260	1	1
E17	104046260	1	2
E17	104046260	1	2
E17	104046165	1	1
E17	104045651	1	2
E17	104045651	1	2

Binary_Bark · Jun 4, 2021

@Sum(select top column and drag down to the last number)

Messugga · Jun 4, 2021

SHL said:
Hi. Need help with a formula for the last column that sums total quantity sold per product per store. I want to enter the formula in D3 and just drag/copy it down.

Store Product Qty Sold Which formula can help sum as below?
B11
104046350
1
2
B11
104046350
1
2
B11
104049971
1
3
B11
104049971
1
3
B11
104049971
1
3
B11
104046260
1
2
B11
104046260
1
2
B11
104045651
1
3
B11
104045651
1
3
B11
104045651
1
3
C12
108017391
1
1
C12
104046112
1
1
C12
104049971
1
2
C12
104049971
1
2
C12
104046260
1
2
C12
104046260
1
2
C12
104046165
1
2
C12
104046165
1
2
E17
108017391
1
1
E17
104046350
1
1
E17
104049971
1
1
E17
104046093
1
3
E17
104046093
1
3
E17
104046093
1
3
E17
104050260
1
1
E17
104046260
1
2
E17
104046260
1
2
E17
104046165
1
1
E17
104045651
1
2
E17
104045651
1
2

Personally, I'd just use a pivot table... not exactly what you asked for but it's the easiest method I can think of.

MidnightZA · Jun 4, 2021

I think the simplest case with a formula is a "sumif".

swift412 · Jun 4, 2021

If Store is in Column A, and Product is in Column B, and Quantity is in Column C, then the formula:

Code:

=SUMIFS(C:C, B:B, B2, A:A, A2)

where 2 is the row that you're entering the formula into. i.e. if the first entry starts on row 3 or 4, use that instead of 2.

swift412 · Jun 4, 2021

Alternatively, as Messugga said, PivotTables were made for this, and they're an amazingly useful tool to figure out if you work with Excel a lot.

Four easy steps:

Click on one of the cells with data on your sheet
Insert tab on the top of your screen, then PivotTable button (usually the first button)
It should pop up a dialog box titled "Create PivotTable" with lots of options. For now you can ignore them all. Just make sure that on the sheet, the selection is around all your rows and columns. Press OK
On this new sheet, you'll see something on the left saying "To build a report...", and on the right, a new panel with your columns in a list on the top, and 4 boxes at the bottom. Just click the tickboxes next to Store, Product and Qty Sold, and Excel ~~will~~ should figure out which boxes to put them in automatically.

SHL · Jun 4, 2021

Messugga said:
Personally, I'd just use a pivot table... not exactly what you asked for but it's the easiest method I can think of.

swift412 said:
If Store is in Column A, and Product is in Column B, and Quantity is in Column C, then the formula:

Code:

=SUMIFS(C:C, B:B, B2, A:A, A2)

where 2 is the row that you're entering the formula into. i.e. if the first entry starts on row 3 or 4, use that instead of 2.

View attachment 1082909

swift412 said:
Alternatively, as Messugga said, PivotTables were made for this, and they're an amazingly useful tool to figure out if you work with Excel a lot.

Four easy steps:

Click on one of the cells with data on your sheet

Insert tab on the top of your screen, then PivotTable button (usually the first button)

It should pop up a dialog box titled "Create PivotTable" with lots of options. For now you can ignore them all. Just make sure that on the sheet, the selection is around all your rows and columns. Press OK

On this new sheet, you'll see something on the left saying "To build a report...", and on the right, a new panel with your columns in a list on the top, and 4 boxes at the bottom. Just click the tickboxes next to Store, Product and Qty Sold, and Excel ~~will~~ should figure out which boxes to put them in automatically.

View attachment 1082917

Thanks a mil. Appreciated

Join the MyBroadband community

Get started

MS Excel - Help with Formula

SHL

Executive Member

Binary_Bark

Forging

Messugga

Honorary Master

MidnightZA

Expert Member

swift412

Expert Member

swift412

Expert Member

SHL

Executive Member