Consulting

Results 1 to 5 of 5

Thread: SUMIFS with Multiple criteria using Variables

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    SUMIFS with Multiple criteria using Variables

    Trying to create a VBA macro using SUMIFS with Multiple criteria:


    eg =SUMIFS(D21940,B2:B1940,"Framing",G2:G1940,"Labor" )
    Because this formula will be replicated in a large range;


    I want to replace "Framing" in the sample formula with Whatever value is in Column AA [ Offset(0, -1) ] as long as its NOT EMPTY/ IsBLANK
    AND
    I want to replace "Labor" with Whatever value is in Row 3 of that Column


    Then I will move on to Column AC and so on.


    See Attached Worksheet
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You don't need vba. Change the formula in AB4 to:
    =SUMIFS($D$2:$D$1940,$B$2:$B$1940,$AA4,$G$2:$G$1940,AB$3)
    Copy down and across.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks p45cal:

    Your formula worked as expected on a worksheet. The reason why I was looking at VBA is because other rows will be added, and a few other considerations.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Make that into a proper Excel table, when you add remove rows the formulae will add/remove themselves.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    p45cal:

    I used Named ranges and implemented the VBA with some minor changes.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •