Cannot get SUM(SUMIF to work with VBA, yields “Run-time error 1004”. SUM(SUMIF is typically used in Excel to sum using multiple criteria. This is my formula.
Worksheets("units").Cells(i, j).FormulaR1C1 = "=pws!R[0]C[0] SUM(SUMIF(pws!R1C3:R1C" & (2 numlxp) & ",central!C[1]R3:C[1]R" & (2 numatt) & ",pws!R[0]C3:R[0]C" & (2 numlxp) & "))"
I also tried a version using OFFSETS instead of R1C1. The OFFSETS versions works within Excel itself, but not in VBA.
I then tried simplifying to the below, to see if there was an issue with SUM(SUMIF specifically within VBA, but it returns a formula with @ for implicit intersectionality and gives the wrong result ‘0’:
Worksheets("units").Cells(2, 2).Formula = "=SUM(SUMIF(pws!C1:L1,central!C3:C6,pws!C2:L2))"
CodePudding user response：
Try using SUMPRODUCT combined with unary operator:
Yellow cells would be the criteria (in your case, central!C3:C6).
Formula in cell C6 is:
=SUMPRODUCT(--(COUNTIF($A$6:$A$7,$C$1:$L$1)>0)*$C$2:$L$2)
Formula in cell C7 is:
=SUMPRODUCT(--($C$1:$L$1=$A$6:$A$7)*$C$2:$L$2)
First formula ignore duplicates in criteria and second one does not. Second one is easier to implement but if your criteria has duplicate values, it will duplicate result, so watch out and choose wisely. Just as example, notice if both of my criterias are the same, each formula returns a different output:
Anyways, when you are done choosing, you should be able to implement this in VBA code like this (or kind of):
Worksheets("units").Cells(2, 2).Formula = "=SUMPRODUCT(--(COUNTIF(central!$C$3:$C$6,pws!$C$1:$L$1)>0)*pws!$C$2:$L$2)"