ExcelBanter - Excel : Nesting of functions such as sumproduct and sumif

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Excel : Nesting of functions such as sumproduct and sumif (https://www.excelbanter.com/excel-worksheet-functions/123545-excel-nesting-functions-such-sumproduct-sumif.html)

Excel : Nesting of functions such as sumproduct and sumif

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

Excel : Nesting of functions such as sumproduct and sumif

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

Excel : Nesting of functions such as sumproduct and sumif

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

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

Excel : Nesting of functions such as sumproduct and sumif

Richard,

Thanks so much. This solved our requirement. Now we can use this formula
widely in our work.

We use the sumproduct formula but did not come accross "--" inside the
formula. if you can please explain the use of this.

Regards,

Nimish

"RichardSchollar" wrote:

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

Excel : Nesting of functions such as sumproduct and sumif

Su

($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

Nimish Shah wrote:

Richard,

Thanks so much. This solved our requirement. Now we can use this formula
widely in our work.

We use the sumproduct formula but did not come accross "--" inside the
formula. if you can please explain the use of this.

Regards,

Nimish

"RichardSchollar" wrote:

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

Excel : Nesting of functions such as sumproduct and sumif

Richard,

Thanks a lot again. This makes us understand very nicely.

Best wishes for this festive season.

Regards,

Nimish

"RichardSchollar" wrote:

Su

($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

Nimish Shah wrote:

Richard,

Thanks so much. This solved our requirement. Now we can use this formula
widely in our work.

We use the sumproduct formula but did not come accross "--" inside the
formula. if you can please explain the use of this.

Regards,

Nimish

"RichardSchollar" wrote:

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