On Thursday, August 9, 2012 6:05:51 AM UTC-5, Living the Dream wrote:
Hi all



I know there's been many threads regarding this subject, but I'm still

none the wiser as to why it is so difficult to get it to work

considering it can be selected from the (.) context menu.



I'm not a huge fan of nesting formula's, especially when it comes to

complex ones over hundreds/thousands of cells given the crappy

infrastructure I deal with, that said!



Can anyone give me a reasonably basic explanation as to why this does

not work as is throws up a Type Mismatch:



So as to give as clearer explanation as possible, here is what each

column has.



Column A = Year ( contains 2011 though to 2012 and running )

Column B = Month ( Numeric representation ( 1 = Jan ))

Columns F & G contain either a 0 or 1.





Sub Calc_Jan()



Dim Ssht As Worksheet, Tsht As Worksheet

Dim mySumA As Range, mySumB As Range, mySumF As Range, mySumG As Range

Dim c1 As Range, c2 As Range

Dim Anchor1 As Range, Anchor2 As Range

Dim myMth As String



Set Ssht = Sheets("KPI Input")

Set mySumA = Ssht.Range("$A$2:$A$2500") 'Numeric Cells

Set mySumB = Ssht.Range("$B$2:$B$2500") 'Numeric Cells

Set mySumF = Ssht.Range("$F$2:$F$2500") 'Numeric Cells

Set mySumG = Ssht.Range("$G$2:$G$2500") 'Numeric Cells



myMth = 1

Set Tsht = Sheets("KPI")

Set Anchor1 = Tsht.Range("$H$17:$H$21") 'Numeric Cells

Set Anchor2 = Tsht.Range("$H$24:$H$28") 'Numeric Cells



For Each c1 In Anchor1

If c1 < "" Then

With c1

.Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c1) *

(mySumB = myMth) * (mySumF 0))

End With

End If

Next c1



For Each c2 In Anchor2

If c2 < "" Then

With c2

.Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c2) *

(mySumB = myMth) * (mySumG 0))

End With

End If

Next c2



End Sub





TIA

Mick.

Cuz you can't do it that way. Either put your formula in the range with the macro and convert to value or use evalueate. Example:

AN = Evaluate("SUMPRODUCT(--($A$2:$A$21=$A8),--($B$2:$B$21=$B8),--($C$2:$C$21=$C8))")