Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 08, 2022
Solved

Got stuck at creating a code for Ifna, sumif and sumproduct

Hi All, would like to create a code for Ifna, sumif and sumproduct.    Tried to create a code replicate the excel formula below: IFNA(IF(LEN(B1)>1,   SUMIF(Sheet1!E:I,B2,Sheet1!I:I),   SUMPROD...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 09, 2022

    hrh_dash 

    See if this does what you want:

        Dim ws As Worksheet
    Set ws = Sheet1
    With ws.Range("C44")
        .Formula = "=IFNA(IF(LEN(C10)>1,SUMIF(Sheet1!E:I,C15,Sheet1!I:I)," & _
            "SUMPRODUCT(MAX(('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B=B2)*" & _
            "('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S=" & _
            "SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B2)*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S)))*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$M2:$M)))," & _
            """Credit limit not provided to this customer"")"
        .Value = .Value
    End With
hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 09, 2022

HansVogelaar , i tried the following but there is some issues with structuring of the code. Would you be able to assist on this?

 

Dim i as Variant
i = ws.Range("C44")

i = WorksheetFunction.IfNa(
If Len(ws.Range("C10")) > 1 Then
WorksheetFunction.SumIf(exA, ws.Range("C15"), exB):=          
Else
Application.WorksheetFunction.SumProduct (WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) *
--(cLS = Application.WorksheetFunction.SumProduct(WorksheetFunction.Max(--(cLB = clRequestWs.Range("B2")) * _
cLS))) * cLM))

End If

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 09, 2022

    hrh_dash 

     

    Why don't you just place the formula in the cell?

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      Jun 09, 2022
      The file will be used across the whole team and do not wish for the formula to be meddle with 😞 it will also be sent to external service provider vendors to help enhance the whole credit assessment process..
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 09, 2022

        hrh_dash 

        See if this does what you want:

            Dim ws As Worksheet
    Set ws = Sheet1
    With ws.Range("C44")
        .Formula = "=IFNA(IF(LEN(C10)>1,SUMIF(Sheet1!E:I,C15,Sheet1!I:I)," & _
            "SUMPRODUCT(MAX(('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B=B2)*" & _
            "('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S=" & _
            "SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B2)*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S)))*" & _
            "'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$M2:$M)))," & _
            """Credit limit not provided to this customer"")"
        .Value = .Value
    End With

Resources