Solved

sumproduct for the first x # of items

Posted on 2011-03-07
11
271 Views
Last Modified: 2012-05-11
I currently have a table that has 5 columns.  The first 2 are criteria columns.  The last three are multiplied together and added.  I am using a formula similar to this.  =SUMPRODUCT(--(Table1[ColumnA]=myFirstCriteria), --(Table1[ColumnB]=mySecondColumn), Table1[ColumnC], Table1[ColumnD], Table1[ColumnE])

I need to be able to say that the first 100 of Column E would be counted.  Something like =MIN(Table1[ColumnE], 100)

If I try to put that into my SUMPRODUCT formula, I get the #VALUE error because 2500 doesn't match the length of the other arrays.

Any help would be appreciated.
0
Comment
Question by:busapps
11 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35063390
Hello,

Do you mean the first 100 rows in the table? Like this?

=SUMPRODUCT(--(Table1[ColumnA]=myFirstCriteria), --(Table1[ColumnB]=mySecondColumn), --(Row(Table1[ColumnA])<=100),Table1[ColumnC], Table1[ColumnD], Table1[ColumnE])

Or do you only want to sum the 100 smallest values? If so, how do you want to handle duplilcates, where several rows have the same number value?

Please explain.

cheers, teylyn
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35063392
You can try offset

=SUMPRODUCT(--(offset(Table1[ColumnA],0,0,100,1)=myFirstCriteria), --(offset(Table1[ColumnB],0,0,100,1)=mySecondColumn), offset(Table1[ColumnC],0,0,100,1), offset(Table1[ColumnD],0,0,100,1), offset(Table1[ColumnE],0,0,100,1))

Thomas
0
 
LVL 1

Author Comment

by:busapps
ID: 35063403
Not exactly what I mean.  Probably should have been more clear.  

My 5th column is a quantity column.  Would look something like this.

ColumnA|ColumnB|ColumnC|ColumnD|ColumnE
MyData| MyData | MyData| 2 | 4 | 75
MyData | MyData | MyData | 5 | 2 | 100

In this example, I'd want to do a Sumproduct of all 75 of the items in the first line and 25 of the 100 in the second line.  
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35063442
Thoroughly confused now. Where are the 75 items? Column E has the cell values 75 and 100. But where is the data you want to sum?

Could you please upload a workbook that shows your data structure? Replace confidential data with dummy data.

It would help to see what you see.

cheers, teylyn
0
 
LVL 1

Author Comment

by:busapps
ID: 35063471
Per request, here is a sample worksheet.  The fromula shows what I'm essentially trying to do.

Thanks,
TestWS.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35063575
Hello,

thanks for the workbook. I'm still not sure, but maybe something like

=SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),--(Table1[ColumnE]<=100),Table1[ColumnC],Table1[ColumnD])

This results in 18

Or for the second tier

=SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),--(Table1[ColumnE]>100),--(Table1[ColumnE]<=250),Table1[ColumnC],Table1[ColumnD])


cheers, teylyn
0
 
LVL 1

Author Comment

by:busapps
ID: 35063591
Not exactly what I'm looking for.  If the first row contained 150, I would still want the first 100 to be picked up.  The <= 0 will pick up EVERY row which has less than 100 rows.  I essentially want a running sum within the sumproduct function.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35063622
>> The <= 0 will pick up EVERY row which has less than 100 rows

What do you mean? How can a row have rows? And where would those rows be?

Can you step back and put into plain Words what you want to calculate. Something along the lines of

"Sum all values in columns C and D where column E has a value between 1 and 100"

That's how I interpreted your statement "First Tier Column E Between 1 and 100" in the file. Apparently that was wrong.

cheers, teylyn




0
 
LVL 1

Author Comment

by:busapps
ID: 35063659
Sorry about the miscommunication:

I want my calcualtion to pick up the first 100 quantity of Column E that meet Criteria 1 and Criteria 2 and multiply by Column C and Column D.

The formula I provided in my workbook should provide some more color around what I am trying to do.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35063707
So, sum all values in C and D that meet the criteria and add only the first 100 rows from column E (that also meet the criteria). Is that it? You'll need to construct two sumproducts and add them together. I don't think you can do it in one.

=SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),Table1[ColumnC]+Table1[ColumnD])+SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),--(ROW(Table1[ColumnE])<=100),Table1[ColumnE])

and for the next tier

=SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),Table1[ColumnC]+Table1[ColumnD])+SUMPRODUCT(--(Table1[ColumnA]="Criteria1"),--(Table1[ColumnB]="Criteria2"),--(ROW(Table1[ColumnE])>100),--(ROW(Table1[ColumnE])<=250),Table1[ColumnE])
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35075912
Hello busapps,

OK, I'm assuming that for your example the result should be 850

2*4*75 = 600 + 5*2*25=250 = 850

It's quite tricky to do I think, I'm assuming that you might have some rows that don't meet the criteria specified so I constructed a slightly larger table with some rows where columns A and B are different. I also added an extra column to the table with this formula in F2 copied down

=SUMIFS(E$2:E2,A$2:A2,A2,B$2:B2,B2)

That gives a running total of column E for each criteria combination in columns A and B

Now if the column A criterion is shown in H2, column B criterion in I2 then you can use this "array formula" in J5

=SUMPRODUCT(--(Table1[ColumnA]=H$2),--(Table1[ColumnB]=I$2),Table1[ColumnC],Table1[ColumnD],IF(Table1[ColumnF]>I5,IF(Table1[ColumnF]-Table1[ColumnE]<I5,Table1[ColumnE]-Table1[ColumnF]+I5),Table1[ColumnE]))-SUM(J$4:J4)

confirmed with CTRL+SHIFT+ENTER, where I5 is the upper bound (100 in your example) and J$4:J4 are the previous results. Note that this approach works assuming you have no gaps in your ranges, e.g. range 1 is 1 to 100 so range 2 must start at 101. You can change J5 to 80 or 120, for example, but I6 should change to 81 or 121 respectively.....

see attached

Note: if you haven't used "array formulas" before then you must use the following procedure if you change or re-enter the formulas

Press F2 key to select formula.
Hold down CTRL and SHIFT keys and at the same time press ENTER so that you see curly braces like { and } around the formula in the formula bar.

regards, barry
26869833.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
change VBA to show result in cells rather than debug.print 4 34
Update As Well As Add 6 38
Excel printing page management 2 20
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now