Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 35

Thread: Can someone help me with this? Probably a beginner level formula.
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    I give up

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  2. #22
    New Member
    Join Date
    Aug 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Quote Originally Posted by kweaver View Post
    Doesn't my last formula (without the restrictions on the rows) do that?

    Excel 2010
    A B C D E F G H I
    3 Flavor Date Location Total
    4 Oreos 8/1/2019 USA 1000 Date Location Flavor Total
    5 Vanilla 8/2/2019 USA 1000 9/5/2019 France 1500
    6 Chocolate 8/3/2019 USA 1000
    7 Strawberry 8/4/2019 USA 4000
    8 Double choc 8/4/2019 USA 1500
    9 Pistachio 8/5/2019 USA 1500
    10 Cream 8/6/2019 USA 1500
    11 Raspberry 8/6/2019 USA 1500
    12 Blueberry 8/8/2019 USA 1500
    13 Mint 8/4/2019 USA 2000
    14 Crunch 8/5/2019 USA 100
    15 Blue Moon 8/4/2019 France 500
    16 Butter Pecan 9/5/2019 France 700
    17 Rocky Road 9/5/2019 France 800
    Sheet11

    Worksheet Formulas
    Cell Formula
    I5 =IF(ISBLANK(H5),SUMIFS(D:D,B:B,F5,C:C,G5),SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5))
    For example, this post here, I was hoping instead of SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5), it would be possible to just do: SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8) and get it to apply for more than just 1 flavor.

  3. #23
    New Member
    Join Date
    Aug 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Quote Originally Posted by sandy666 View Post
    I give up

    Have a nice day
    Sorry, not sure if I did or said something wrong. I'm not against pivot tables, I just was trying to give more information to be sure it will be the correct solution for me here since I don't know much about them and wasn't sure I gave enough detail for what I was trying to do.

  4. #24
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    nothing wrong

    all what you need is Append all single tables into one (Power Query) then create Pivot Table
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #25
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Quote Originally Posted by Python49 View Post
    For example, this post here, I was hoping instead of SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5), it would be possible to just do: SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8) and get it to apply for more than just 1 flavor.
    Hi, you can do it like this.

    Excel 2013/2016
    ABCDEFGHI
    1
    2
    3FlavorDateLocationTotal
    4Oreos08/01/2019USA1000DateLocationFlavorFormula
    5Vanilla08/02/2019USA100008/01/2019USAOreos3500
    6Chocolate08/01/2019USA1000Vanilla
    7Strawberry08/01/2019USA4000Chocolate
    8Double choc08/04/2019USA1500Cream
    9Pistachio08/05/2019USA1500
    10Cream08/01/2019USA1500
    11Raspberry08/06/2019USA1500
    12Blueberry08/08/2019USA1500
    13Mint08/04/2019USA2000
    14Crunch08/05/2019USA100
    15Blue Moon08/04/2019France500
    16Butter Pecan09/05/2019France700
    17Rocky Road09/05/2019France800

    Sheet1



    Worksheet Formulas
    CellFormula
    I5=SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8))

    [code]your code[/code]

  6. #26
    Board Regular
    Join Date
    Mar 2018
    Posts
    55
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Power pivot would be the best way to handle both the complex question (query) and the amount of tables. It really is a no brainer. Also the flexibility of a pivot tables when tour boss says "now show me it this way" is so invaluable that its laughable.

    Pivot tables might be a bit scary but they are worth the investment, I would argue they are almost as valuable as macros when you consder effort spent on solving a problem. What would take many hours coding and setting up pivot tables takes an hour or two to set Up.

  7. #27
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    or you can go with a VBA solution where i think as long as all 31 sheets are the same it would be useful.
    do you have a master list of flavors on a separate sheet by chance?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  8. #28
    New Member
    Join Date
    Aug 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Quote Originally Posted by FormR View Post
    Hi, you can do it like this.

    Excel 2013/2016
    A B C D E F G H I
    1
    2
    3 Flavor Date Location Total
    4 Oreos 08/01/2019 USA 1000 Date Location Flavor Formula
    5 Vanilla 08/02/2019 USA 1000 08/01/2019 USA Oreos 3500
    6 Chocolate 08/01/2019 USA 1000 Vanilla
    7 Strawberry 08/01/2019 USA 4000 Chocolate
    8 Double choc 08/04/2019 USA 1500 Cream
    9 Pistachio 08/05/2019 USA 1500
    10 Cream 08/01/2019 USA 1500
    11 Raspberry 08/06/2019 USA 1500
    12 Blueberry 08/08/2019 USA 1500
    13 Mint 08/04/2019 USA 2000
    14 Crunch 08/05/2019 USA 100
    15 Blue Moon 08/04/2019 France 500
    16 Butter Pecan 09/05/2019 France 700
    17 Rocky Road 09/05/2019 France 800
    Sheet1

    Worksheet Formulas
    Cell Formula
    I5 =SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8))
    For me, when I do this, as soon as I type in H5:H8 it returns the value of 0. But if I type a single cell such as H5 then it returns correctly. Although this thread and the feedback from everyone has led to me realizing 2 things:

    1) Given the type of data that I'm dealing with, it would be worth it for me to learn how to use pivot tables and some of the other more powerful queries. I hadn't before since we didn't really start out requiring the level of detail in the tables that we have now.

    2) If I simply add the flavor category to the data on the 31 sheets where the raw data is added, then I can do SUMIFS for that column instead of for the flavor column.

    A B C D E F G H I
    1
    2
    3 Flavor Date Location Flavor Group Total
    4 Oreos 08/01/2019 USA Cookies 1000 Date Location Flavor Group Total
    5 Vanilla 08/02/2019 USA Vanillas 1000 08/01/2019 USA Chocolates 3500
    6 Chocolate 08/01/2019 USA Chocolates 1000
    7 Strawberry 08/01/2019 USA Fruits 4000
    8 Double choc 08/04/2019 USA Chocolates 1500
    9 Pistachio 08/05/2019 USA Classic 1500
    10 Cream 08/01/2019 USA 1500
    11 Raspberry 08/06/2019 USA Fruits 1500
    12 Blueberry 08/08/2019 USA Fruits 1500
    13 Mint 08/04/2019 USA 2000
    14 Crunch 08/05/2019 USA 100
    15 Blue Moon 08/04/2019 France 500
    16 Butter Pecan 09/05/2019 France 700
    17 Rocky Road 09/05/2019 France 800


    Nonetheless, having to resort to this solution illustrates why I'm still a beginner in Excel Will begin some studying on pivot tables.
    Last edited by Python49; Sep 17th, 2019 at 08:39 AM.

  9. #29
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can someone help me with this? Probably a beginner level formula.

    Quote Originally Posted by Python49 View Post
    For me, when I do this, as soon as I type in H5:H8 it returns the value of 0.
    If you want to progress with this can you post the exact formula you ended up using and a small set of sample data that demonstrates the problem.

    Quote Originally Posted by Python49 View Post
    2) If I simply add the flavor category to the data on the 31 sheets where the raw data is added, then I can do SUMIFS for that column instead of for the flavor column.
    This sounds like a good way forward to me, especially if you don't get on well with the pivot tables.
    [code]your code[/code]

  10. #30
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Can someone help me with this? Probably a beginner level formula.

    worth to read:

    PowerQuery aka Get&Transform (links):
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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