| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT + CSE | Excel Worksheet Functions | |||
| Sumproduct (Sumif) with Nested Or Criteria | Excel Worksheet Functions | |||
| SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
| SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions | |||
| Sumif not Sumproduct | Excel Worksheet Functions | |||
Linear Mode
