Excel : Nesting of functions such as sumproduct and sumif

[Guest]

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

 

[RichardSchollar]

Hi Nimish

Isn't this simply a matter of SUMming the Total kgs(Tom) range eg:

=SUM(D2100)

or is it more complicted than this?

Richard

 

[Guest]

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(D2100)

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]

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

 

[RichardSchollar]

Sure:

($C$2:$C$6=G1)

This pasrt of the formula results in an array of True/Falses dependent
on whether C2:C6 contains whatever value is in G1, so if G1 is Tom it
returns

{TRUE,FALSE,TRUE,FALSE,TRUE}

Great! But you want this as proper numeric values (ie 1=True,0=False)
so to get that we use the double minus ie -- which performs this
conversion.

Alternatively, we could have used +0,*1,^1 as they all do the same. So:


=SUMPRODUCT(A2:A6,B2:B6,(C2:C6="Tom")+0)

=SUMPRODUCT(A2:A6,B2:B6,(C2:C6="Tom")*1)

=SUMPRODUCT(A2:A6,B2:B6,(C2:C6="Tom")^1)

Should all work equally well.

Does this help?

Richard