Hi guys,
I'm trying to use a sumproduct to multiply values adjacent to two criteria that are in the same column and that repeat at random intervals down the column.
My formula: SUMPRODUCT($F$6:$F$16,($E$6:$E$16=J17)*($E$6:$E$16=I17))
I am trying to make the sumproduct do the following: (100 x 5) + (200 x 7) + (400 x 3) = 3,100
Please take a look at the worksheet attached which explains better what i'm trying to do. Any help would be appreciated
Cheers
Mike
Try in "K17"Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Please try and press Ctrl+Shift+Enter
=SUM(INDEX(F:F,N(IF(1,SMALL(IF(E6:E20=I17,ROW(F6:F20)),{1,2,3}))))*INDEX(F:F,N(IF(1,SMALL(IF(E6:E20=J17,ROW(F6:F20)),{1,2,3})))))
Or below with Ctrl+Shift+Enter if more than 3 sets of data
=SUM(INDEX(F:F,N(IF(1,SMALL(IF(E1:E999=I17,ROW(F1:F999)),ROW(INDIRECT("1:"&COUNTIF(E:E,I17)))))))*INDEX(F:F,N(IF(1,SMALL(IF(E1:E999=J17,ROW(F1:F999)),ROW(INDIRECT("1:"&COUNTIF(E:E,I17))))))))
Try array entering this.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Edited to simplify formula.
Last edited by FlameRetired; 10-15-2018 at 04:15 AM. Reason: simplify formula
Dave
Thanks guys. My question, why can't a standard sumproduct work on this table? How would the table need to be changed to accommodate a simpler formula. All the formulas provided work however it would be difficult for someone else to maintain this workbook if they came across these more complicated formulas. Note - The first formula (above) is simple however would be very time consuming on a larger data set.
You could do simple SUMPRODUCT if you are willing to restructure the tables into one with each Hectares & Machine $/Ha paired values on the same rows. Without that the random intervals are going to complicate things.
A standard SUMPRODUCT needs to have identical numbers of rows. The table structures you have are irregular and you are wanting to multiply paired values that are offset from one another randomly. This can be resolved as avk has done with nested steps and some manual work or by calculations like with the SUBTOTAL/OFFSET scheme.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks