+ Reply to Thread
Results 1 to 5 of 5

How to create an array SUMIF with multiple criteria

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Romania
    MS-Off Ver
    2019
    Posts
    7

    How to create an array SUMIF with multiple criteria

    Hello peeps,

    You've been super helpful to me in the past when I got stuck, so I'm hoping you can help me sort this out again.
    I have attached a test sheet showing you what I've done so far.

    In the VisitsTracker sheet, I have a layout containing all of the weeks of the year, as well as their subsequent months. For each week, I am tracking a metric - here it is under "Accounts Reached ↓".

    In the DataTab sheet, I want to be able to calculate on a monthly basis this "reach". To do so, I am planning to first create four columns, each containing week numbers for the first weeks, second weeks, third, and fourth/fifth (I'm merging these last two where that's the case) weeks of the month. You can see an early iteration of that in the DataTabs under "Intervals".

    Through this array formula: {=SUM(SUMIF(VisitsTracker!$C$3:$C$258,H2:H14,VisitsTracker!$A$6:$A$261))}, I was able to go through the VisitsTracker sheet and check whether the week number I am checking is within one of the intervals. But I need one more condition. Right now it adds every value it finds whose week number corresponds to a number within my intervals. But I want to keep it on a monthly basis.

    Meaning that I need to check extra if the month I am looking at is January (in this case), and only add the value if that's also the case. Any ideas how on earth I could do that?

    Thank you very much in advance for your time!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: How to create an array SUMIF with multiple criteria

    do you mean along lines of:

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

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: How to create an array SUMIF with multiple criteria

    alternatively, you could dispense with the matrix of weeks etc and just use:

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

  4. #4
    Registered User
    Join Date
    03-11-2021
    Location
    Romania
    MS-Off Ver
    2019
    Posts
    7

    Re: How to create an array SUMIF with multiple criteria

    Thank you very much for your thoughtful response. However, I am learning as I go here, and can't really fully grasp your solution. And it seems to return an error when I try to apply any of it. Any chance you could apply in on my test sheet and attach it? It would certainly help me 'reverse engineer' it and understand it better. Thanks again!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: How to create an array SUMIF with multiple criteria

    See attached with XLent formulae
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Max of sumif array with multiple criteria
    By DRFILL in forum Excel General
    Replies: 9
    Last Post: 04-06-2017, 10:32 PM
  2. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. Array Formula w/ Multiple SumIf Criteria
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  6. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2005, 04:05 PM

Tags for this Thread

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