+ Reply to Thread
Results 1 to 3 of 3

Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    Newcastle, England
    MS-Off Ver
    2013
    Posts
    26

    Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!

    Hi there,

    I am using excel 2013 and am really struggling to come up with a solution to the following problem:

    I look after a spreadsheet that keeps track of the mandays of a collection of projects by week and is laid out thus:

    Month: Feb Feb Feb Mar Mar Jun Jun Jun Jul Jul
    Project Name

    Project 1 10 20 10 25 15 10 20 10 20 15
    Project 1 10 20 10 25 15 10 20 10 20 15
    Project 2 10 20 10 25 15 10 20 10 20 15
    Project 2 10 20 10 25 15 10 20 10 20 15


    I want to sum the total value of all the cells that fall at the intersection of Project 1 and Feb (I've obviously anonymised the data here). The formula needs to array all the manday cells so that if I add another line for Project 1 and add more mandays, it will automatically sum them to the total.

    Here's the bit that makes it slightly trickier though....

    Mixed in with the manday data there are cells that contain text, so it looks more like the following:

    Month: Feb Feb Feb Mar Mar Jun Jun Jun Jul Jul
    Project Name

    Project 1 10 20 10 25 15 10 A 10 20 15
    Project 1 W 20 P 25 15 B 20 10 20 15
    Project 2 10 20 10 25 15 10 20 10 20 O
    Project 2 10 D 10 25 15 R 20 10 20 15


    I've played around with different formulas with a whole load of different combinations of SUMIF/SUMIFS/SUMPRODUCT/INDEX/MATCH and array formulas, which baffled me. The two main problems I've encountered are formulas returning a #VALUE error because of the text cells included in the array and INDEX & MATCH formulas only summing the first match returned.

    Any help on this would be sincerely and greatly appreciated - please let me know if any further information is required.

    EDIT: So I’m new to this forum and have just realised that posting has changed the format of the examples I gave above. The project names are in column A and the months are in row 1, the mandays are at intersections between the two.

    Cheers,

    Chris
    Last edited by TheClaw2323; 03-06-2015 at 04:55 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!

    Try this Array Formula...
    Sum Array.xls

    Remember, since this is an Array Formulas, you must use Ctrl-Shift-Enter (then the magical curly brackets appear)...

    Let us know if you have any questions...

  3. #3
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Need a SUMPRODUCT/SUMIF/INDEX/MATCH array formula. Please Help!!

    This option can also nice.
    Attached Files Attached Files

+ 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] Index/Match to Return 1D array to be used in a sumproduct
    By david0985 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2014, 04:41 PM
  2. [SOLVED] index match and sumproduct or sumif on multiple criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 06:20 AM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Sumproduct, Sumif, or Array Formula?
    By ExcelQuestion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2007, 07:16 PM
  5. [SOLVED] INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements?
    By Ronny Hamida in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-27-2006, 04:35 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