Calculate the total Qty

[firstWeMeet]

Hello, just having difficulties on how to calculate the Qty from my huge data, the data contains 13 columns, but the data which to compute is column D, I and K.
the column D is the Name, the I is the Item code, the K is the Qty, so I need to compute the Qty base from the Name and item code. how to execute it?

 

[Akashwani]

Hi,

Does this work for you?

Sample data and example result...

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Name					Item Code		Qty		Name	Item Code	Total Qty
2		Ben					99999		100		Ben	99999	200
3		Bob					99998		200				200
4		Bud					99997		300
5		Bill					99996		400
6		Brad					99995		500
7		Ben					99999		100
8		Bob					99998		200
9		Bill					99997		300
10		Brad					99996		400
11		Bud					99995		500
12		Ben					9999		100
13
Sheet1

The formula in O2 will work in Excel 2007 and later.
The formula in O3 will work in all versions of Excel.

I hope that helps.

Ak

 

[firstWeMeet]

is it possible if Im going to add the column H?

 

[Akashwani]

Hi,

Just change the column references from K to H....

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Name				Qty	Item Code				Name	Item Code	Total Qty
2		Ben				100	99999				Ben	99999	200
3		Bob				200	99998						200
4		Bud				300	99997
5		Bill				400	99996
6		Brad				500	99995
7		Ben				100	99999
8		Bob				200	99998
9		Bill				300	99997
10		Brad				400	99996
11		Bud				500	99995
12		Ben				100	9999
13
Sheet1

I hope that helps.

Ak

 

[Aladin Akyurek]

Hi,

Does this work for you?
...

Spreadsheet Formulas

Cell Formula O2 =SUMIFS($K$2:$K$12,$D$2:$D$12,M2,$I$2:$I$12,N2) O3 =SUMPRODUCT(--($D$2:$D$12=M2)*($I$2:$I$12=N2),K2:K12) <tbody> </tbody>

<tbody>
</tbody>

...
Ak

Your SumProduct formula is not well-formed...

It is:

Either...

=SUMPRODUCT(--($D$2:$D$12=M2),--($I$2:$I$12=N2),K2:K12)

Or...

=SUMPRODUCT(($D$2:$D$12=M2)*($I$2:$I$12=N2),K2:K12)

Or...

=SUMPRODUCT(($D$2:$D$12=M2)+0,($I$2:$I$12=N2)+0,K2:K12)

but not:

=SUMPRODUCT(--($D$2:$D$12=M2)*($I$2:$I$12=N2),K2:K12)

 

[Aladin Akyurek]

is it possible if Im going to add the column H?

Which columns do you want to sum: K and H, or just H?

 

[Akashwani]

Hi Aladin,

Thanks for the pointers, I use the -- and *, as that is the only "structure" I can remember!!
What is the main differences between the one I use and the alternatives you have provided, is it processing speed?

Thanks

Ak

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the pointers, I use the -- and *, as that is the only "structure" I can remember!!
What is the main differences between the one I use and the alternatives you have provided, is it processing speed?

Thanks

Ak

It's about consistency and coherency. It might even cause incorrectness.

 

[VBABEGINER]

Good Post Akashwani, Aladin sir. It helps me somewhere.