Summing with Multiple Criteria over 3 Worksheets

[Kooksmagooks]

Hello,

I am sure this question has been asked before but I have looked in lots of different threads and can't seem to get the proposed solutions to work so apologies for the repeat request.

I am setting up a report that pulls data in from multiple sources (3 different databases) and builds up month on month as the year rolls on (so I can calculate Year to Date sales figures). Each data extract has some information unique to itself and some in common with one or both of the other databases. I am looking to Sum the common data, based on multiple criteria into a summary report, so I have assumed I need a Sumproduct variation but I am not sure exactly what.

Because the data extracts are from different sources I have to house them on 3 different worksheets, but the information is formatted in the same way, all common fields are spelled the same and they have the same column headings. Data is in the same location on the worksheets, although the length of the extracts vary.

For example on Sheet 1 the table is housed in E503:AP709, Sheet 2 E503:AP564, Sheet 3 E503:AP779. I am unsure if the fact that they have a different number of Rows might scupper me using Sumproduct..

The data tables that start in cells E503 are not the actual data extracts but are the result of formula reading off the data pulls (I can only get information from the databases in one month blocks so I have to Sum them to get running Year to Date totals.

The criteria I want to use to return my result are in columns E (measure, so either value or volume) and F (which is a list of products or brand names) and the (first) range I want to sum is in column G, which is sales figures for 2012. I hard coded column E and F as I thought them being formula might be one of the issues with my sumproduct.

I have tried to do the following so far:

- Create a named range of my worksheets called YTD_Ranges (=Sheet1:Sheet3!$E$503:$AU$800). I went up to AU800 in the selection as I tried to select a number of cells that would cover the largest of the extracts and also allow for extra columns as the time periods built up. I don't have data in there at the moment, but it is populated with formula returning 0s

- Use various combination of Sumproduct Sumif Indirect to return a result but I keep getting a #Value error. I'm just don't understand the syntax correctly and can't even say which step I might have got wrong.

Option 1 (where $F7 is a corresponding list of brand names in my summary shell)
=SUMPRODUCT(--(INDIRECT("'"&YTD_Ranges&"'!$F$503:$F$800")=$F7),--(INDIRECT("'"&YTD_Ranges&"'!$E$503:$E$800")="Volume"),(INDIRECT("'"&YTD_Ranges&"'!$G503:$G800")))

Or I have tried
=SUMPRODUCT(SUMIF(INDIRECT("'"&YTD_Ranges&"'!$E$503:$E$800"),"Volume",(SUMIF(INDIRECT("'"&YTD_Ranges&"'!$F$503:$F$800"),$F7,INDIRECT("'"&YTD_Ranges&"'!$G503:$G800")))))

Any ideas where I could be going wrong or if what I am trying to do it even possible? Sorry for not attaching an example, it is not information I can share, but if it would help people to visualise it I can dummy it up.

Some posts I have looked at had the data sum (so my column G) at the beginning of the formula but I always thought it was at the end.

Any help would be gratefully received!

Thanks

[XOR LX]

Hi,

I feel kind of sorry telling you this after you went to all the effort to write that comprehensive and lucid description of your problem, but in this case I think a sample, mocked-up spreadsheet really will be like a picture equalling a thousand words.

Regards

[Kooksmagooks]

Oh no I totally agree. I have done this mock up (I can't share the actual file). I hope this is enough to give the idea of what I am aiming for.

[XOR LX]

Ok, that's great, but I'm slightly confused as to where I should be looking to pull the data and, although you've put in some comments along the lines of "This would be the sum of volume and Brand 1 on all 3 sheets", it would be much more useful if you could enter some actual desired results and re-post (if necessary adding a little more dummy data in the source sheets).

Regards