Hello everybody,
I really could need some big help here...
I have a workbook that contains 29 worksheets (represent countries). Each worksheet has the same set-up and is used to track 1 project per line.
In a summary sheet, I would like to summarize the number of projects across all worksheets that fulfill a certain set of criteria.
The criteria are defined in several columns (all text) as follows:
A: Month (Values: JAN, FEB, ...)
D: Status (Values: open, ongoing, cancelled)
J: Service Type (Values: new, modification)
K: Service Item (Values: A, B, C,.. )
Hera an example what I want to summarize:
Count all projects in all country worksheets which are in JAN, open or ongoing (<>cancelled) ,new, and belongs to type A.
I have defined all country sheet names in a vertical range (M5:M32) and called the Range "MySheets".
For above example I tried this function, but I guess it's completely wrong..
=SUMPRODUCT(SUM((INDIRECT("'"&MySheets&"'!$A$4:$A$999")=B$7)*(INDIRECT("'"&MySheets&"'!$D$4:$D$999")<>"cancelled")*(INDIRECT("'"&MySheets&"'!$J$4:$J$999")=$T$3)*(INDIRECT("'"&MySheets&"'!$K$4:$K$999")=A$43)))
Can anyone please please help me to find a solution for this?
Thank you so much!!!
Ciao Lea
(PS: Using Excel 2010)
Bookmarks