Complicated SUMIFS formula help (VBA)

[unicornsla]

Hello all,

I am creating a UDF in VBA for a workbook. The workbook has two sheets: a Budget sheet and aPurchase Order (PO) sheet. Essentially, POs are entered into the PO sheet and the SUMIFS will go thru ALL the PO $ amounts and (hopefully) deduct the respective amounts from the proper account in the Budget sheet.

If desc Like "*CR#*" Then
    pos = InStr(1, desc, "CR")
    num = CStr(Val(Mid(desc, pos + 2, 2)))
    calcbudget = WorksheetFunction.SumIfs(POamts, POdesc, "*CR" & num & "*", POdesc, "<>*CR" & num & "#*", POcostel, costel, POwbs, wbs)

Above, I am trying to account for a situation where "...CR#..." appears in the description of aBudget account. That same "CR#" will appear in the description of any PO withdrawing from that Budget account. (That "POcostel, costel, POwbs, wbs" just deals with making sure the PO has the same general ledger account and WBS code as the Budget account we want to deduct from)

The problem is if a PO has description: "...CR301...", it will be deducted from the Budget account with description "...CR3...". I tried using the

"<>*CR" & num & "#*"

as another conditional but I'm not sure if the # wildcard has been removed.


Many thanks!

 

[unicornsla]

To clarify, below is an example of accounts from Budget:

...and suppose these are two purchase orders. I don't want the CR32 amount to get deducted from my "CR3 - Russia NRE" Budget account.