I have a file,contains 12 sheets ( Month wise ) and each sheet have approx. 400000 rows.Now,I want to make a summary sheets by given some input datas and expect the required datas from the sheets.


For an example,if ,I want to give the input as plant-HHHD and Month-Oct,2011,then, represent the Oct'2011 datas and plant HHHD only.Sometimes,it is to be happen,that, plant may be HHHD and HHHG and month may be Oct,Nov and Dec.In that case, the summary sheet should be shown as the sum of both of plants and also the sum of months.
Is it possible through an excel formula?

For more clarification,please refer the attached file.
(Pl.to be note that each sheet of attached file size has reduced )


If it is possible,put the excel formula on attached file.

Regards

Pradeep Kumar Gupta

Attached Files

New Folder.zip (168.0 KB, 14 views)

Hi Pradeep,

I don't think that's practicable with a formula. For example, for just one Plant in C4, for one month in C5 (which should be formatted as TEXT so that the input sheet name isn't converted to a date), the required formula for C15, which can be copied across & down to D22, is:
=SUMIFS(INDIRECT("'"&$C$5&"'!$R:$R",TRUE),INDIRECT ("'"&$C$5&"'!$Q:$Q"),$B15,INDIRECT("'"&$C$5&"'!$E: $E"),C$14,INDIRECT("'"&$C$5&"'!$B:$B"),$C$4)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]