+ Reply to Thread
Results 1 to 5 of 5

Sum Sumifs or sumproduct using multiple criteria with matching months

  1. #1
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Sum Sumifs or sumproduct using multiple criteria with matching months

    I use a sum(sumifs function to match several criteria to determine the % of a specific products volume is on each pc of equipment. I now need to add another criteria to match the month but don't know how to do it. I've tried simply adding the "monthly criteria (show in attachment) and have also tried using a SUMPRODUCT formula but still can't figure it out.

    Any suggestions?
    Attached Files Attached Files
    Last edited by billrogers184; 05-28-2019 at 02:16 PM. Reason: Providng new example and description

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Sum Sumifs or sumproduct using multiple criteria with matching months

    I probably would have used a match and offset (check them out) but continuing with your theme:
    =SUMPRODUCT($E$7:$P$18*($E$6:$P$6=U$6)*($B$7:$B$18=$R7)*($C$7:$C$18=$S7)*($D$7:$D$18=$T7))/SUMPRODUCT($E$7:$P$18*($E$6:$P$6=U$6)*(($B$7:$B$18="B")+($B$7:$B$18="G")+($B$7:$B$18="E"))*($C$7:$C$18=$S7)*($D$7:$D$18=$T7))

    Adjust as necessary.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Sum Sumifs or sumproduct using multiple criteria with matching months

    Please try at U21:AF27

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: Sum Sumifs or sumproduct using multiple criteria with matching months

    Bo-Ry, I don't understand the CHOOSE(MATCH(9,INDEX(FIND($R21,{"AG","BDE","CF"}),)),{"A","G"},{"B","D","E"},{"C","F"}) part of the formula. I tired to use the evaluate formula function to follow it but get lost after the Find function. Is the find function saying the letter "B" can exist within the combinations of {"AG","BDE","CF"}? The Index function results in #Value!,1,#Value!? what is the Match 9 and the Choose function doing? Can you clarify what each step is doing and the order it is happening?

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Sum Sumifs or sumproduct using multiple criteria with matching months

    =FIND($R21,{"AG","BDE","CF"}) = {#VALUE!,1,#VALUE!} this give 1 or 2 or 3 depend on R21 and #VALUE!,

    For R25
    =FIND("E",{"AG","BDE","CF"}) = {#VALUE!,3,#VALUE!}
    no "E" in "AG" =#VALUE!, "E" at 3rd position of "BDE" =3, again no "E" in "CF" =#VALUE!

    =Match(9, INDEX({#VALUE!,3,#VALUE!},)) =2 Give position of the last number that less or equal to 9 any number more than 3 will work.
    Index just for avoid pressing Ctrl+Alt+Enter

    =CHOOSE(2,{"A","G"},{"B","D","E"},{"C","F"}) ={"B","D","E"} return 2nd set of value or array.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  2. SUMIFS with months, names, values Multiple Criteria Based on Months and Names
    By Aaron R Bauer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-25-2016, 12:07 PM
  3. SUMIFS with months, names, values Multiple Criteria Based on Months and Names
    By Aaron R Bauer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-14-2016, 06:51 PM
  4. [SOLVED] SUMIFS formula that takes first seven of matching criteria from an array
    By Samuel_j in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-24-2015, 05:08 AM
  5. [SOLVED] sumproduct Or sumifs on multiple criteria matching day & time
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-16-2015, 05:45 AM
  6. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1