VBA "sumifs" code allows "IF" function for sub-criteria

  • The database on the attached file gives sales details from three outlets for a product ("Widget") that also has an option ("Boxed")

    NB gave me an elegant formula to cope with blank cells in the option field, but I now need a VBA solution.

    The Code below should search the database for the various criteria and paste the answers into the list on Sheet 1.

    I set up named Ranges and DIMmed them all BUT the Code generates a "Compile error" when it gets to the "If" statement whether the "Option" field is blank or not (the same issue I had when looking for the formula approach).

    Hope someone can resolve this.



  • Re: VBA "sumifs" code allows "IF" function for sub-criteria

    Can you explain what the Function is supposed to do? I'm a little confused :)

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: VBA "sumifs" code allows "IF" function for sub-criteria

    Hello, yegarboy,

    The code has to do the following:

    "D2" should show how many Widgets (Range "PRODUCT") without any sub-criteria (Range "SUBSET") were sold by Store 1 in the month selected in C1 (Range "LOOKFOR").
    The Code SHOULD do this by matching everything sold by everyone (Range "SALES) against the PRODUCT and SUBSET sold by Outlet 1 .in the month selected (LOOKFOR).
    "D3" then matches the total sales (Range "SALES") to see how many Widgets (Range "Product") that also have a sub-criteria "Boxed" (Range "Subset") were sold by the same store in the same period.
    Cols E and F do the same for the other two outlets.

    CHALLENGE: The Code needs to distinguish between a "Widget" (Data in "Product" but "SUBSET" as blank) and a "Boxed Widget" (data in both "Product" and "SUBSET" columns). Without that it adds up ALL Widget sales in Col L (because it adds up everything saying "Widget" in Column H) THEN "double counts" the "Boxed Widgets" (because it selects every row with "Widgets" in Col H and "Boxed" in Column "I").

    DB solved the problem of the "blank cell" wiht a formula that had an "IF" statement in the middle of the calculation. When I translated that to VBA the code refused to accept it and threw up a "Compile Error" statement.

    Two quick points: This is a sample based on a rather large and complex spreadsheet so it is not possible to simply add "Unboxed" as a SUBSET. Nor (sadly) is it possible to take all the "Widget" sales, deduct "Boxed Widgets" and poste the balance as "Widgets".

    Hope this clarifies


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!