Return sum of values in column when matching and also when Row matches input

[Dave855]

In the attached sheet, I have many products listed in columns, and the columns repeat with the following (Purc. #, Cost, Cust. Inv )

I am needing help making a formula that will, sum the total cost for the a specified Purch. # and specified product id.

I have tried all sorts of ways to do this but a search based on two inputs is too difficult for myself to works out. I thought it would be easy at first, but no luck after trying for hours.

I hope someone has an idea how this can be solved and Iam fascinated with what the solution is.

Thanks
Dave

[tom1977]

Array formula (accepted Ctrl + Shift + Enter)

Please Login or Register  to view this content.

in L3

for start

[Azam Ali]

try
=SUMIF(OFFSET(1:1048576,6,MATCH($L$1,6:6,0)-1,20000,1),$L$2,OFFSET(1:1048576,6,MATCH($L$1,6:6,0),20000,1))

[TMS]

Building on Tom's answer, this might work ... or at least go a step further:

=SUMIF(OFFSET($A$7,0,MATCH(E2,6:6,0)-1,1000,1),E1,OFFSET($B$7,0,MATCH(E2,6:6,0)-1,1000,1))

Adjust the 1000 to be the maximum number of rows of data.


Regards