Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

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

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

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

    I want to do SUMIFs for the following image but for more than one flavor criterion. The two flavors highlighted in yellow both have the same date and location, so I'd like to be able to get the total for them both without just simply typing another +SUMIFS formula for the second flavor. Mainly because the list of flavors can be very long, so therefore the formula would become too long. I've toyed around with some SUMPRODUCT formulas but can't get one to sum multiple flavors with multiple qualifiers.

    https://drive.google.com/open?id=13yEfHm5cbggEuCAjffk4UjVm4tonCB-4

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    Can you paste the sample data here please?

    HAve a look here for posting data to the forum

    https://www.mrexcel.com/forum/about-...tachments.html

    Sounds like a SUMPRODUCT would do the trick.
    Last edited by Michael M; Sep 16th, 2019 at 07:31 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    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.

    Flavor Date Location Total
    Oreo 8/1/2019 USA 100
    Vanilla 8/2/2019 USA 1000 =SUMIFS(D4:D8,B2:B11,F6,C2:C11,G6,A2:A11,H6)
    Chocolate 8/3/2019 USA 1000
    Strawberry 8/4/2019 USA 1000 Date Location Flavor
    Passion 8/4/2019 USA 1500 8/4/2019 USA Strawberry
    Double Choc 8/5/2019 USA 1500
    Pistachio 8/6/2019 USA 1500
    Cream 8/7/2019 USA 1500
    Raspberry 8/8/2019 USA 1500
    Blueberry 8/9/2019 USA 1500

    I know how to get the sumtotal for all the strawberrys on the list according to date and locations, but I want to be able to get the total for multiple flavors according to date and location. Found it faster to just type the details as an example here than post screenshot.
    Last edited by Python49; Sep 16th, 2019 at 08:01 PM.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Am I over-simplifying what you want with this: =SUMIFS(D4:D12,B4:B12,F5,C4:C12,G5) which will exclude the flavor and just look for the date and location match.

    Or, if the table increases: =SUMIFS(D:D,B:B,F5,C:C,G5)
    Last edited by kweaver; Sep 16th, 2019 at 08:14 PM.

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

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

    did you try a PivotTable?
    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

  6. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    You might also put an I column next to your criteria table and use this: =IF(ISBLANK(H5),SUMIFS(D:D,B:B,F5,C:C,G5),SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5)) in I5.

  7. #7
    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
    Am I over-simplifying what you want with this: =SUMIFS(D4:D12,B4:B12,F5,C4:C12,G5) which will exclude the flavor and just look for the date and location match.

    Or, if the table increases: =SUMIFS(D:D,B:B,F5,C:C,G5)
    I will have a list of say 100 flavors actually and would like to take the totals for various combinations of flavors from a certain date for a certain location. So out of 100 flavors on a list (all with their own date, location, and total) I'd want say the total for 5 of the flavors that all match a particular date and location. So in the table above, I'd need a formula that can tell me the total for both strawberry and passion since they're both having the same date and location (total would be 2500)

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Doesn't my last formula (without the restrictions on the rows) do that?

    Excel 2010
    ABCDEFGHI
    3FlavorDateLocationTotal
    4Oreos8/1/2019USA1000DateLocationFlavorTotal
    5Vanilla8/2/2019USA10009/5/2019France1500
    6Chocolate8/3/2019USA1000
    7Strawberry8/4/2019USA4000
    8Double choc8/4/2019USA1500
    9Pistachio8/5/2019USA1500
    10Cream8/6/2019USA1500
    11Raspberry8/6/2019USA1500
    12Blueberry8/8/2019USA1500
    13Mint8/4/2019USA2000
    14Crunch8/5/2019USA100
    15Blue Moon8/4/2019France500
    16Butter Pecan9/5/2019France700
    17Rocky Road9/5/2019France800

    Sheet11



    Worksheet Formulas
    CellFormula
    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))

    Last edited by kweaver; Sep 16th, 2019 at 08:44 PM.

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    This without the flavours

    Code:
    =SUMPRODUCT(--(B2:B15=F3)*(C2:C15=G3)*(D2:D15))

    This with a choice of flavours

    Code:
    =SUMPRODUCT(--(B2:B15=F3)*(C2:C15=G3)*(A2:A15=H3)*(D2:D15))
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    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/6/2019 USA 1500
    9 Pistachio 8/6/2019 USA 1500
    10 Cream 8/6/2019 USA 1500
    11 Raspberry 8/6/2019 USA 1500
    12 Blueberry 8/6/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))
    It's possible I didn't explain what I meant well but in your table here, what I'm trying to get is a formula which will let me get the total for a select number of flavors on the list and get the total. So in your above table, I want to be able to select all the flavors which need to be included in the totals. For instance, if we were to use flavors 4-9 only (oreos to pistachio), I'd want to know the total for only those flavors, which have a date of 8/6/2019 and location of USA. The total would be 3000 for just double chocolate and pistachio. If we then change the selected flavors instead to 4-8 only (oreos to double choc) then now the total would just be 1500 for double choc. If we keep flavors 4-8 now and change the date to be 8/1/2019 then it would give a total of 1000 only for Oreos.

    In the formula above, it seems to only be taking SUMIFS for one particular flavor, unless I'm doing it incorrectly.

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
  •