lookup formula that finds values to sum across sheets

[Solola]

I'm working on a summary sheet in Excel 2007 that sums values from all preceeding sheets (which have the same row and column headings, but in different row/columns) and displays them on a single sheet. Essentially, it's a rollup of all detail sheets.

The issue I have is that I need the formula to do a lookup to each of the detail sheets to find the value to sum, since the intersection of total revenue for the current month, for example, might be in cell A1 on the first sheet, and A4 on the second sheet, and B2 on the third, and so on.

I realize I can write a series of 10+ vlookup or index/match formulas to find the intersection, and add them together in my formula, but I was wondering if there was a way to write a single formula that would find them.

I tried an array formula, but this errored out with a #REF error. (You must not be able to use this across multiple sheets?)
{=SUM(IF('PE160:PE306'!$C$1:$C$155=$C14,'PE160:PE306'!D$1:D$155,0))}
(where my sheets range from PE160 to PE306, with many more in between, and within those sheets, my row headers are in column C, my row heading lookup value from my rollup sheet is in cell C14, and the values to be summed are in column D. In this example, I'm assuming that the columns are consistent across sheets...but I'd ideally like to do a lookup for that, too.)

I also tried a sumproduct, but it doesn't appear to work across sheets, either.

Does anyone have a solution for me, please? Thanks so much for your help!

 

[Comfy]

I cannot comment about if it can be done via formula alone.

But would Consolidate data in multiple worksheets - Excel - Office.com be an option.

Or maybe a custom function?

 

[Shweta]

Hi Soloba,

Visit on Excel VBA Tips: ADD VALUES FROM DIFFERENT SHEETS OF A WORKBOOK WITH A CRITERIA

Regards,
Shweta