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

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

3. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`

Check the attached file:-

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

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