Be seen. Boost your question’s priority for more expert views and faster solutions

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.

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.

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

=SUMPRODUCT(--(Table1[Colu

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

=SUMPRODUCT(--(offset(Tabl

Thomas

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

ColumnA|ColumnB|ColumnC|Co

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.

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

Thanks,

TestWS.xlsx

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

=SUMPRODUCT(--(Table1[Colu

This results in 18

Or for the second tier

=SUMPRODUCT(--(Table1[Colu

cheers, teylyn

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

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.

=SUMPRODUCT(--(Table1[Colu

and for the next tier

=SUMPRODUCT(--(Table1[Colu

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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

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

850It'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

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[Colu

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