Results 1 to 2 of 2

Thread: Activating multiple sheets with countifs function

  1. #1

    Activating multiple sheets with countifs function

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I am trying to use a countifs function to get my data on multiple sheets. The code is as follows :

    ActiveSheet.Range("S1").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""P"",ActiveSheet!I:I,""1"")")
    ActiveSheet.Range("S2").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""I"",ActiveSheet!I:I,""1"")")
    ActiveSheet.Range("S3").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""O"",ActiveSheet!I:I,""1"")")
    ActiveSheet.Range("S4").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""C"",ActiveSheet!I:I,""1"")")
    ActiveSheet.Range("S5").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""L"",ActiveSheet!I:I,""1"")")

    As the sheet name varies, I'm not able to use the constant name Sheet1!. I tried changing (Sheet1!D,""P"",Sheet1!I:I,""1"") to (ActiveSheet!D,""P"",ActiveSheet!I:I,""1"") but it doesn't seem to work. Any idea how to do it? Any help will be appreciated. Thank you.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version

Posting Permissions

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