Excel : Nesting of functions such as sumproduct and sumif
Nimish
Many apologies - now I understand!
You can use:
=SUMPRODUCT(A2:A6,B2:B6,--(C2:C6="Tom"))
where the criteria could be a cell reference holding the value Tom or
Harry, so you could use:
=SUMPRODUCT($A$2:$A$6,$B$2:$B$6,--($C$2:$C$6=G1))
and if G1 holds "Tom" (without the quotes) and G2 holds Harry, you can
copy the formula down eg H1:H2 and it will give you the relevant
totals.
Does this make sense?
Best regards
Richard
Nimish Shah wrote:
Hi Richard,
Thanks for prompt reply.
There is a complication. I will try to re-explain my problem and give the
example again below. I wish i could send you the excel file which i cannot
attach in this e-mail through this site.
Qty-Pcs Kg-Each Manufacturer Total Kgs
100 1 Tom 100
150 2 Harry 300
50 3 Tom 150
150 4 Harry 600
300 5 Tom 1500
Total 2650
i can create a column of total kgs by the formula eg
=a2*b2
i can then see the full total kgs by using formula eg
=SUM(D2:D100)
i can also see the full total kgs(without creating a separate column) by
using formula eg
=SUMPRODUCT(A2:A6,B2:B6)
I need formula/function which can diretly give me the following result
without creating the column of Total kgs(Tom) or Total kgs(Harry).
We also use sumif formula but did not work. i tried nesting(mixing of
sumproduct and sumif) but with no success. Maybe you can help and solve.
How to calulate in one fuction the following :-
1. Total Kgs-Tom (1750) :
2. Total Kgs-Harry (900) :
Regards,
Nimish
"RichardSchollar" wrote:
Hi Nimish
Isn't this simply a matter of SUMming the Total kgs(Tom) range eg:
=SUM(D2:D100)
or is it more complicted than this?
Richard
Nimish Shah wrote:
I need guidance to calculate the following which is shown as an excample:-
Qty-Pcs Kg-Each Manufacturer Total kgs(Tom) Total kgs(Harry)
100 1 Tom 100
150 2 Harry 300
50 3 Tom 150
150 4 Harry 600
300 5 Tom 1500
How to calulate in one fuction the following :-
1. Total Kgs-Tom (1750) :
2. Total Kgs-Harry (900) :
Regards,
Nimish Shah
|