+ Reply to Thread
Results 1 to 7 of 7

Count the records basing 2 different cells

  1. #1
    Registered User
    Join Date
    10-23-2009
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Count the records basing 2 different cells

    Question
    I have a simple workbook with 5 spreadsheets and I need to get Excel 2007 to count the number of closed records on spreadsheet 4 based on date where

    'Week 4'!I7:I66 is Date
    AND
    'Week 4'!G7:G66 = Closed

    I should have been able to figure this out, but I am stumped.

    Thanks,
    Rahul

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count the records basing 2 different cells

    Given use of XL2007

    =COUNTIFS('Week 4'!I7:I166,your date,'Week 4'!G7:G66,"Closed")

  3. #3
    Registered User
    Join Date
    10-23-2009
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Unhappy Re: Count the records basing 2 different cells

    Thanks for the quick reply....but it didnt work. I tried

    =COUNTIFS('Week 4'!I7:I67,22-Oct,'Week 4'!G7:G66,"Closed")

    it gave me #VALUE!. Can you help me please..

    thank you
    Rahul

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count the records basing 2 different cells

    Firstly, ensure the two ranges are of equal size

    Second, you would need to pass the date criteria slightly differently, ie:

    =COUNTIFS('Week 4'!I7:I67,"22-Oct-09",'Week 4'!G7:G67,"Closed")

  5. #5
    Registered User
    Join Date
    10-23-2009
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Smile Re: Count the records basing 2 different cells

    YES...that worked....thank you...
    one more doubt in this....

    the solution is for a single spredsheet.....how can i count the same from 2-3 spreadsheets in same workbook

  6. #6
    Registered User
    Join Date
    10-23-2009
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Thumbs up Re: Count the records basing 2 different cells

    Never mind....found the answer....thank you for your help Sir....

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count the records basing 2 different cells

    If you have only 2-3 sheets it is better to use 3 COUNTIFS and sum them, ie:

    =SUM(COUNTIFS('Week 2'!I7:I67,"22-Oct-09",'Week 2'!G7:G67,"Closed"),COUNTIFS('Week 3'!I7:I67,"22-Oct-09",'Week 3'!G7:G67,"Closed"),COUNTIFS('Week 4'!I7:I67,"22-Oct-09",'Week 4'!G7:G67,"Closed"))

    "elegant" formulae in terms of 3D conditional summation are not efficient.

+ 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