Problem using SUM(If( Rather than SumIf(

Question

I have written a Formula in Excel that Sums up Items in 1 Column (Text) the Column is a Dynamic Named Range (Invoice_list_Item) the Formula sets a Value (Of my Choice) based on each Item in the Range and Sums those Values, I am now using a Userform where I would like to Display that Equation rather than in the workbook itself, so my problem is that everytime I write this code in the userform it doesn't work; either throwing an error, or not displaying a value. I have tried Application.Sum, Application.Sumif, Application.Sumproduct and the main one I've been trying to make work Application.Sum(If(Range(

Any Help in correcting this Formula in to a Macro will be much appreciated.

=SUM(IF(Invoice_list_Item="1/4 Item",0.25,0)+IF(Invoice_list_Item="1/2 Item",0.5,0)+IF(Invoice_list_Item="1 Item",1,0))

Answer 1

You can use Application.Evaluate (this works for array formulae) to evaluate a string as if it was an Excel formula:

 Dim vResult As Variant
    vResult = Application.Evaluate("=SUM(IF(Invoice_List_Item=""1/4 Item"",0.25,0)+IF(Invoice_List_Item=""1/2 Item"",0.5,0)+IF(Invoice_List_Item=""1 Item"",1,0))")