Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT...
Hi all...
Hoping you can help... I'm fairly new to Excel but have been picking up bits and pieces (mainly from this forum!!!).... I've got a worksheet which has 20 columns and usually around 500 rows of data. I am trying to use it to total the number of referrals received from specific sources and for specific reasons...as there are over 15 sources and 5 reasons, I have had to try to come up with a formula which will 'pick and choose' the correct elements. To do this I am using the following forumla: =SUMPRODUCT(--($W$2:$W$1000="Criteria 1"),--(!$V$2:$V$1000="Criteria 2"),--( $O$2:$O$1000<"Branches")) This works very well, and returns the actual volume of hits. However, I am also trying to incorporate values. For each row, there will be a monetary value (in coumn N). What I would like to do, is modify the formula I am using so that it will total the values of the rows which meet my criteria. Hope this makes sense? Any help would be gratefully received Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT...
Hi Matt
Just add in the column N to your Sumproduct formula as below =SUMPRODUCT(--($W$2:$W$1000="Criteria 1"), --(!$V$2:$V$1000="Criteria 2"), --($O$2:$O$1000<"Branches"), --($N$2:$N$1000)) Strictly speaking, you do not need the --( ) around the range containing your amounts to be summed, as these are numeric values already, but it is easier to see the general case by including them. All the other sets of conditions are tests of True or False and the double unary minus --( ) is coercing them into 1 for True and 0 for False so they can be multiplied together in the array created by Sumproduct. For each row, if all other conditions are met then you will have 1 x 1 x 1 x Value in N Any 0 with the other conditions will cause the whole row to be 0. You might like to consider the use of a Pivot Table with this data in order to be able to quickly change or select different criteria for analysis. For more help on setting up Pivot tables go to Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables or Mike Alexander's site http://www.datapigtechnologies.com/ExcelMain.htm http://www.datapigtechnologies.com/f...es/pivot1.html -- Regards Roger Govier "matt_n" <u28095@uwe wrote in message news:6809b1626b510@uwe... Hi all... Hoping you can help... I'm fairly new to Excel but have been picking up bits and pieces (mainly from this forum!!!).... I've got a worksheet which has 20 columns and usually around 500 rows of data. I am trying to use it to total the number of referrals received from specific sources and for specific reasons...as there are over 15 sources and 5 reasons, I have had to try to come up with a formula which will 'pick and choose' the correct elements. To do this I am using the following forumla: =SUMPRODUCT(--($W$2:$W$1000="Criteria 1"),--(!$V$2:$V$1000="Criteria 2"),--( $O$2:$O$1000<"Branches")) This works very well, and returns the actual volume of hits. However, I am also trying to incorporate values. For each row, there will be a monetary value (in coumn N). What I would like to do, is modify the formula I am using so that it will total the values of the rows which meet my criteria. Hope this makes sense? Any help would be gratefully received Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT...
Roger
Thank you very much for your help! Something which has had me confused for so long can be solved so very very quickly - and simply!! Think its indicative of my Excel experiences...!! But hey, lilve and learn! I have tried using pivot tables, but I am drawing data from quite a few different sources, plus I have monthly files which then have data transferred to an annual file, so I have found them to be a bit cumbersome. My present attempt spreads across 5 worksheets, with each containing 8 pivot tables!! Thought I would experiment with this method as it cuts out all the other sheets! Thanks anyways, and your help is very much appreciated Matt Roger Govier wrote: Hi Matt Just add in the column N to your Sumproduct formula as below =SUMPRODUCT(--($W$2:$W$1000="Criteria 1"), --(!$V$2:$V$1000="Criteria 2"), --($O$2:$O$1000<"Branches"), --($N$2:$N$1000)) Strictly speaking, you do not need the --( ) around the range containing your amounts to be summed, as these are numeric values already, but it is easier to see the general case by including them. All the other sets of conditions are tests of True or False and the double unary minus --( ) is coercing them into 1 for True and 0 for False so they can be multiplied together in the array created by Sumproduct. For each row, if all other conditions are met then you will have 1 x 1 x 1 x Value in N Any 0 with the other conditions will cause the whole row to be 0. You might like to consider the use of a Pivot Table with this data in order to be able to quickly change or select different criteria for analysis. For more help on setting up Pivot tables go to Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables or Mike Alexander's site http://www.datapigtechnologies.com/ExcelMain.htm http://www.datapigtechnologies.com/f...es/pivot1.html Hi all... [quoted text clipped - 32 lines] Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |