+ Reply to Thread
Results 1 to 6 of 6

SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple sheets.

  1. #1
    Registered User
    Join Date
    09-22-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    13

    Question SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple sheets.

    I have a Summary sheet that references other sheets in the workbook that are updated monthly by an external party.

    The sheets are inconsistent in the number of rows and columns but there are values in the columns with the same name across sheets which don’t always appear in column D, sometimes they appear in column F or AZ for example but the sheet names and column names that I need to pull the data out of are always the same. This led me to INDEX, MATCH, MATCH but then...

    When values in column A (which contains a list of names) meets a certain criteria e.g. if name contains “DBN”, I then need to validate whether there is an “x’” on the same row in a column named “Blue” and where both these criteria are met, add up all the values in the column named “Cost” on the Summary sheet.

    I tried creating an array by referencing Sheet2!$A:$A as the entire column then using INDEX, MATCH, MATCH for the entire sheet (Sheet2!$1:$1048576) but Excel runs out of memory as it’s parsing the entire sheet across 5 sheets in multiple functions and crashes.

    I tried creating an array automatically based on cells with data in them using COUNTA in a formula but couldn’t get it to work. Then I started reading up about the pitfalls of using arrays and decided that there are better ways to pull the data, like SUMPRODUCT / SUMIFS combined with INDEX, OFFSET and MATCH to no avail.

    I’m sure there must be a more elegant way of doing this. PowerPivot possibly?

    Cost with multiple criteria in non standard sheets.xlsx

    Many thanks

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple she

    We can achieve this using Sumproduct() Indirect() Sumif(s) Match() Index() combination but it will be very cumbersome approach

    Why don't you consolidate all the data in a single sheet so that it will be easy to arrive the result easily


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    09-22-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple she

    I thought it was a cumbersome approach and not easy to maintain either...

    It does make sense to consolidate all data into one table. Will dig about a bit to find out how to do that (newbie and a little old school)

    I've just gotten my hands on Excel 2013, a bit late I know, but saw PowerView and PowerPivot which look brilliant but I played with them a bit and couldn't figure a way to express the output in a valuable way in this instance. Will try to stay on topic though as the output from this may make from some pretty cool charts and stuff.

  4. #4
    Registered User
    Join Date
    09-22-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple she

    I managed to count the "x" in Column "Blue" quite cleanly using

    =SUM(COUNTIFS(Planes!$A:$A,"*DBN*",(INDEX(Planes!$1:$1048576,0,MATCH("Blue",Planes!1:1,0))),"x"))

    and I replace "Planes" in the formula with names of the other sheets but Excel runs out of memory enumerating the whole sheet so would be nice to nail that one down too

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple she

    Here is my approach.
    Using SUMIFS with NAMED RANGES will do it.
    With that following formula will do it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file:-
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  6. #6
    Registered User
    Join Date
    09-22-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    13

    Cool Re: SUMIFS, INDEX, MATCH, OFFSET, COUNTA. Multiple criteria across multiple she

    That's great Vikas, thank you.

    I've sorted out my NAMED RANGES and refined counting where there is an x in the column then applied a variation of your SUMIFS formula which brought through the total Disk Capacity per environment.

    Cloud Server List - Clean.xlsx

    Thanks for all your assistance in pointing me in the right direction

    Kind regards
    Dave
    Last edited by TheKlaw; 02-09-2015 at 12:17 AM. Reason: Updated NAMED RANGES and COUNTIFS formulae

+ 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] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  2. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  3. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  4. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  5. Index Match Multiple Criteria Multiple Sheets
    By sctraffic in forum Excel General
    Replies: 10
    Last Post: 07-21-2011, 03:47 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