sumproduct for the first x # of items
Posted on 2011-03-07
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.