VBA SUMIFS

[amuthan10]

I want to use SUMIFS with multiple criteria. I want one of my criteria to be a absolute cell reference containing some text. How do i do that?
concatenation or double quotes dont work. Please help me out. Ive attached an image. I want sheet1 B45 in its place which contains text as criteria

 

[Trevor_S]

If you put a " in the middle of a string, VBA interprets it as closing the string, and will try to interpret what follows as VBA code. But Chr(34) returns the same character. So if you need to use one mid-string, you need to close the first bit of the string, add the character and start the second part - something like

... " & Chr(34) & " ...

 

[Trevor_S]

Unfortunately as I'm using a phone, your screen picture is too small for me to read - but say you were trying to do a formula:
=SUMIFS(A:A,B:B,"ABC")
in the code you would need:

"=SUMIFS(A:A,B:B," & Chr(34) & "ABC" & Chr(34) & ")"

 

[amuthan10]

Unfortunately as I'm using a phone, your screen picture is too small for me to read - but say you were trying to do a formula:
=SUMIFS(A:A,B:B,"ABC")
in the code you would need:

"=SUMIFS(A:A,B:B," & Chr(34) & "ABC" & Chr(34) & ")"

Unfortunately as I'm using a phone, your screen picture is too small for me to read - but say you were trying to do a formula:
=SUMIFS(A:A,B:B,"ABC")
in the code you would need:

"=SUMIFS(A:A,B:B," & Chr(34) & "ABC" & Chr(34) & ")"

I dont want a string as a criteria. i want it to address a particular cell which contains a string.
For example, B4 contains the word ABCD.
I want it to take the string of B4 as criteria instead of me typing "ABCD" myself so that if i change B4 to "XYZ", my formula gets updated.

 

[Trevor_S]

In which case, it should be easier, the formula should appear in VBA the same as it would in an excel cell, e.g.

"=SUMIFS(A:A,B:B,B4)"

Are you perhaps referring to other sheets - if so your excel formula might contain single quotes around the sheet name, which you might need to replace with a different Chr number (39 I think), as single quotes are interpreted as the start of a comment.

 

[amuthan10]

In which case, it should be easier, the formula should appear in VBA the same as it would in an excel cell, e.g.

"=SUMIFS(A:A,B:B,B4)"

Are you perhaps referring to other sheets - if so your excel formula might contain single quotes around the sheet name, which you might need to replace with a different Chr number (39 I think), as single quotes are interpreted as the start of a comment.

Yes it is present in another sheet.
So it should be " & chr(39) sheetname chr(39) & " instead of B4?

 

[amuthan10]

Yes it is present in another sheet.
So it should be " & chr(39) sheetname chr(39) & " instead of B4?

And yeah forgot to mention im using RC convention, so can i use b4 directly in this convention?

 

[Trevor_S]

I've managed to look at your code on a larger screen. I've also managed to try the line out in Excel, and (to my surprise!) the single quotes don't cause an issue. Presumably because it can tell that they're within the double quotes.
R1C1 formulae use the square brackets to denote offsets. Without square brackets, they are fixed. So based on sheet1 B45 as I your original post, I think your code should be:

ActiveCellFormulaR1C1 = "=SUMIFS('PO Dump'!C[-17],'PO Dump'!C[-22],R[0]C[-18],'PO Dump'!C[-19],'sheet1'!R45C2)"