VBA Sumifs

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
798
Office Version
2016
Platform
Windows
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
Joined
Sep 18, 2013
Messages
3,567
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
Joined
Jul 23, 2010
Messages
798
Office Version
2016
Platform
Windows
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
Joined
Oct 27, 2005
Messages
18,304
Office Version
2013
Platform
Windows
Have you turned off screenupdating and turned calculation to manual ?
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
798
Office Version
2016
Platform
Windows
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
 

Forum statistics

Threads
1,085,542
Messages
5,384,321
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top