+ Reply to Thread
Results 1 to 3 of 3

How to make a formula meet two criteria, including a range of different sheets

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    Jersey, Channel Islands
    MS-Off Ver
    Office 2010
    Posts
    25

    How to make a formula meet two criteria, including a range of different sheets

    Hi again All!

    I was wondering if any of your lovely, intelligent people out there would be able to suggest a Formula that would enable me to count how many rows meet a set of two criteria, but, accross multipage worksheets.

    I have already got the working single page formula to count how many rows meet two criteria, and it appears thusly: " =SUMPRODUCT(--('Client List 2013'!C2:C270=2005),--('Client List 2013'!J2:J270="Yes")) " However, I need the "Client List 2013" toalso include "Client List 2012", "Client List 2011", "Client List 2010", "Client List 2009", "Client List 2008", "Client List 2007", "Client List 2006", "Client List 2005" in both the "2005" section and the "Yes" section. In order to be able to find out how many entries I have that meet the crieteria of "2005" and "Yes" across these 9 Worksheets.

    Here's hoping that you can help!

    Many Thanks,
    Martin

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to make a formula meet two criteria, including a range of different sheets

    It would be easier if you were to put that formula in the same cell (eg X1) of each worksheet, suitably adjusted to suit each sheet. So, your formula would be:

    =SUMPRODUCT(--(C2:C270=2005),--(!J2:J270="Yes"))

    You can do this in one operation by grouping the sheets together, selecting X1 and typing that formula, then un-grouping the sheets.

    Then you can add them all together with this:

    =SUM('Client List 2005':'Client List 2013'!X1)

    assuming that your tabs are arranged physically in that order.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-20-2012
    Location
    Jersey, Channel Islands
    MS-Off Ver
    Office 2010
    Posts
    25

    Re: How to make a formula meet two criteria, including a range of different sheets

    Okay, thank you very much for your speedy reply, I will try it out now, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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