Sumproduct with different ranges

[Kenny J]

Howdy

I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me.

Here is the data:
Sheet1: Sheet2:
Widget Parts Location 1 Widget Count Parts
A 4 A 2 20
B 1 C 6
C 2
Location 2 Widget Count Parts
B 3 3

What I'm looking for is the total number of parts at that location, so here the answer would be 20 (2 x 4)+(6 x 2) = 8 + 12 = 20 for location 1, and 3 (3 x 1) = 3 for location 2.

There are multiple locations and a lot more widgets than written here.

Any ideas anyone ???

Thanks

KJ

[sglife]

Your written down sample confused me.
Can you attach a dummy file?
Which cell indicates the location?

[Kenny J]

Ok, attached is a file of an example of what I'm trying to do. The yello highlight is where i want the answer (but this is not important), the blue is what the result should be, and the #N/A is the code I've been trying to get to work (ie: different range dimensions).

Hope this helps to paint a clearer picture as to what is required.

Thanks

KJ

[JBeaucaire]

These are just straightforward VLOOKUP.

[Kenny J]

Thanks JBeaucaire, this is one solution I thought of, but there are a few problems:
1) The names of the widgets are not as nice as the simplistic ones I laid out in my question, they contain letters and numbers and not always in alphabetical order (which vlookup requires). I suppose I could sort the Widget list, however;
2) the number of widgets at each location could be 10-20, making the formula quite long adding them up and also requires a change to formula if a widget is added to a location.

This was the reason behind sumproduct.
I apologise for steering you in the wrong direction with my simplistic data.
Anyone other suggestions?

[Paul]

My suggestion is that you post a more realistic example of your data. Also, VLOOKUP does not require that the lookup range be in alphabetical order, and it can certainly find text strings with letters and numbers (and characters, too).

Look in Excel Help for VLOOKUP, paying close attention to the 4th argument.

[Kenny J]

My suggestion is that you post a more realistic example of your data. Also, VLOOKUP does not require that the lookup range be in alphabetical order, and it can certainly find text strings with letters and numbers (and characters, too).

Look in Excel Help for VLOOKUP, paying close attention to the 4th argument.

I stand corrected - my apologies. I suppose the bigger issue is that vlookup can only take a single argument, not an array like sumproduct.

Further to this, I have been attempting to increase the size of the array so that it matches the other using a function, however it's spitting out a #Value error.

Function filled_array(ref() As Variant, count As Integer) As Variant
   Dim ref()
   Dim count As Integer
   Dim top As Integer
   
   top = UBound(ref, 1)
      
   ReDim ref(count)
   ReDim Preserve ref(count)
   
   For i = ref(top) To UBound(ref, 1)
       ref(i) = 0
   Next
   filled_array = ref
   
End Function

As an example, I would use the following call from excel:

=sumproduct((filled_array(A1:A2,3))*(B1:B3))

This would increase the first array to the same size as the second and therfore would be able to sumproduct them. If I can get this bit working then I can use the same technique to my original question, that is, sumproduct with different ranges.

[Kenny J]

Solved it, kudos to me:

Option Explicit

Function fA(ref As Range, count As Integer) As Variant
   
    Dim i As Integer
    Dim top As Integer
    Dim vA() As Variant
    ReDim vA(count - 1)
   
    top = ref.Rows.count
         
    For i = 0 To top - 1
        vA(i) = ref.Cells(i + 1, 1)
    Next i
         
   fA = vA
   
End Function

Used as:

=Sumproduct((fA(A1:A2,3)=(B1:B3))*(C1:C3))



Works a treat.