# VBA Sumifs

#### hrayani

##### Well-known Member
Hi friends,

How to write this formula in VBA....

=IF(AND(\$C\$1="ALL",\$C\$2="ALL"),SUMIFS(orders_quantity,orders_po_shipment_date,">="&F\$2,orders_po_shipment_date,"<="&F\$4,orders_ref,\$B11),IF(AND(\$C\$1="ALL",\$C\$2<>"ALL"),SUMIFS(orders_quantity,orders_supplier,\$C\$2,orders_po_shipment_date,">="&F\$2,orders_po_shipment_date,"<="&F\$4,orders_ref,\$B11),IF(AND(\$C\$1<>"ALL",\$C\$2="ALL"),SUMIFS(orders_quantity,orders_customer,\$C\$1,orders_po_shipment_date,">="&F\$2,orders_po_shipment_date,"<="&F\$4,orders_ref,\$B11),IF(AND(\$C\$1<>"ALL",\$C\$2<>"ALL"),SUMIFS(orders_quantity,orders_supplier,\$C\$2,orders_customer,\$C\$1,orders_po_shipment_date,">="&F\$2,orders_po_shipment_date,"<="&F\$4,orders_ref,\$B11),""))))
I only want the result not the formula in range H11 to H403.

Regards,

Humayun

#### StephenCrump

##### Well-known Member
You could do it like this:

Code:
``````With Range("H11:H403")
.Formula = "=IF(AND(\$C\$1=""ALL"",\$C\$2=""ALL""),SUMIFS(orders_quantity,orders_po_shipment_date,"">=""&F\$2,orders_po_shipment_date,""<=""&F\$4,orders_ref,\$B11),IF(AND(\$C\$1=""ALL"",\$C\$2<>""ALL""),SUMIFS(orders_quantity,orders_supplier,\$C\$2,orders_po_shipment_date,"">=""&F\$2,orders_po_shipment_date,""<=""&F\$4,orders_ref,\$B11),IF(AND(\$C\$1<>""ALL"",\$C\$2=""ALL""),SUMIFS(orders_quantity,orders_customer,\$C\$1,orders_po_shipment_date,"">=""&F\$2,orders_po_shipment_date,""<=""&F\$4,orders_ref,\$B11),IF(AND(\$C\$1<>""ALL"",\$C\$2<>""ALL""),SUMIFS(orders_quantity,orders_supplier,\$C\$2,orders_customer,\$C\$1,orders_po_shipment_date,"">=""&F\$2,orders_po_shipment_date,""<=""&F\$4,orders_ref,\$B11),""""))))"
.Value = .Value
End With``````

#### hrayani

##### Well-known Member
Hi Stephen,

Thanks for the solution... Its working OK

But, its taking more or less same time as with the formulas in there. Is there a better way to handle it - to make it work faster

Regards,

Humayun

#### Michael M

##### Well-known Member
Have you turned off screenupdating and turned calculation to manual ?

#### hrayani

##### Well-known Member
Hello Micheal,

Yes I did at the start of the code and turned it back on at the end of the code.

Still with formulas in there works a bit faster as compared to VBA..

Regards,

Humayun