Replace SUMIF, not calculating

[CRVerdusco]

I have a sheet that is a master billing for 4 properties. The sumif is used to create a subtotal for each column with a row for each property. in column B user puts in a property code that is the constant in a sumif. Is there a way to do this with out sumif. The issue I am having is that it doesn't update properly. I can calculate it and everything works but when you open the workbook it shows incorrect values.

'Calculates Totals for Westport Conference Center
Public Function TotalWCC(Subtotal As Range)

    TotalWCC = WorksheetFunction.SumIf(Range("B4:B80"), WPConf, Subtotal)
End Function
'Calculates Totals for Westport Plaza
Public Function TotalWP(Subtotal As Range)
    TotalWP = WorksheetFunction.SumIf(Range("B4:B80"), WPPlaza, Subtotal)
End Function
'Calculates Totals for Westport Sheraton Chalet
Public Function TotalCH(Subtotal As Range)
    TotalCH = WorksheetFunction.SumIf(Range("B4:B80"), WPChalet, Subtotal)
End Function
'Calculates Totals for DoubleTree Westport
Public Function TotalDTW(Subtotal As Range)
    TotalDTW = WorksheetFunction.SumIf(Range("B4:B80"), WPDblTree, Subtotal)
End Function

 

[pplstuff]

You could use =sumproduct() instead of sumif().

Sumproduct multiplies, then sums. So the formula would look something like...

=sumproduct(b4:b80,--(WPConf="XXXX"))

Where XXXX would be the property code for the Westport Conf. Ctr.

 

[CRVerdusco]

Thank you for your response, can you elaborate more on how this would work. I would also prefer to have this as a user function for usability reasons.

Currently WPConf is a public const that I called from another module. b4:b80 is where excel looks for the property code and then it should pull values from "subtotal" which is a range inputted into the user defined function.

based on your suggestion and research into INDEX I was trying the following but get #VALUE error

Public Function TotalDTW(Subtotal As Range)

TotalDTW = WorksheetFunction.SumProduct((("$b$4:index($b:$b, counta($b:$b))") = WPDblTree) * Subtotal)
End Function