+ Reply to Thread
Results 1 to 1 of 1

Count entries per row across multiple sheets with fulfill multiple criteria

  1. #1
    Registered User
    Join Date
    12-07-2011
    Location
    Taipei
    MS-Off Ver
    Excel 2003
    Posts
    1

    Count entries per row across multiple sheets with fulfill multiple criteria

    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)
    Last edited by Lea82; 01-11-2013 at 02:42 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1