Page 2 of 2 FirstFirst 1 2
Results 11 to 20 of 20

Thread: Advanced function using multiple Excel files.

  1. #11


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

    Thanks Bob, that worked.

  2. #12
    How should one go about counting the number of postal codes (say that contain "M*") between specified dates?

  3. #13
    =COUNTIFS('[SUBSCR.xls]SUBSCR'!$B:$B,">="&B8,'[SUBSCR.xls]SUBSCR'!$A:$A,"M*")

    I tried the above formula to no avail. B8= 11/27/11

  4. #14
    You can always try to use the <> when needing to exclude info

  5. #15
    Hey MZING81,

    I'm not sure how I can use that.
    I've attached the two workbooks for everyone to checkout. Archive.zip

    The correct number that the formula in C12 is supposed to output is 191.

    Regards,
    Yoshi

  6. #16
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    I think your problem is because Form-D.xlsx is set to 1904 date system.

  7. #17
    Yep, I set it to 1904 date system and it shows the same erroneous amount.
    The total should be about 189.

  8. #18
    It should be counting for all dates great or equal to B8 (11/27/11).

    I think Excel is including dates that have 11 at the beginning of the date, not just at the end.

  9. #19
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    No, it is including anything after 27th Nov 2007, four years earlier than your date in B8, because of the 1904 date system. You need to uncheck that option in Excel options.
    Last edited by Bob Phillips; 2012-12-06 at 09:34 AM.

  10. #20
    You're absolutely right Bob!

    I did not notice the date change to 2007 then I had another look.

    Thanks

Page 2 of 2 FirstFirst 1 2

Tags for this Thread

Posting Permissions

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