Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2010 - Unsure How to Proceed

    Good Day Everyone!

    I'm hoping someone could help me figure out how to accomplish the following in Excel 2010:

    - I have a workbook with 16 sheets (one for each year 1997-2012) containing data on individual files. The individual file information is input horizontally (with date in column 'A', name in column 'B' company number in column 'C' and so on...)
    - There are a different number of files for each year (1997 has 4, 2010 has 30)
    - On sheet 17 I have put the data analysis
    - I am looking for a formula that will tell me how many times 'tbd' appears in the worksheet 1997 where the company number in the same row is '1'. Put another way, on sheet 1997 how many 'tbd's (to be determined) are there for company number 1?

    I have tried countifs and sumproduct etc. but nothing seems to work correctly. I am hoping there is a formula someone might know of that will search through entire columns as opposed to having to name a specific range (ie search C:C instead of C4:C10).

    If anyone is able to provide assistance it would be greatly appreciated!!

    Thanks!!!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Emily,

    Welcome to the lounge as a new poster.

    Attached is one possible solution. It does however require the use of helper columns to determine the row of the company in the yearly sheets and the years have to be changed if you copy the formulas horizontally but they copy vertically just fine. I'll work on it some more so maybe you can copy w/o any editing. Hope this helps. Also the formulas can probablly be merged to eliminate the the helper cells.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Emily,

    Here's the version w/o helper cells and the formulas are fully copyable.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    May 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sample.xlsx

    Thank you very much for your welcome message and assistance with my excel issue!

    I attempted using the version without helper columns but couldn't get it to work. I know I'm doing something wrong somewhere along the line...

    However, I could get the version with helper columns to work great! However, where there is more than one mention of the same company the formula just accounts for the first instance. Is there a way to amend the formula to account for multiple mentions of the same company? I have attached a sample workbook as an example.

    Thanks again for all your help!!

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Emily,

    I got the no helper cell formula to work on your data:
    =COUNTIF(INDIRECT(LEFT(B$3,4) & "!G" & MATCH($A4,INDIRECT(LEFT(B$3,4) & "!$D:$D"),0) & ":N" & MATCH($A4,INDIRECT(LEFT(B$3,4) & "!$D:$D"),0)),"TBD")

    However, I can't seem to figure our how to make it see more than one row in a sheet. I toyed around with SumProduct which seems to be the answer but I haven't used it enough to know why my attempts cause errors. Hopefully someone else will jump in who has more experience with the versitle SumProduct function. If not I'll tackle it via VBA when I have a chance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you convert the tables on the sheets to actual Tables, it's pretty straightforward. See attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Jun 2012
    Location
    Livermore, California
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I couldn't figure out how to not have a set column range, but did come up with a formula that I believe gives you the correct answers your looking for. I'm sure there is a better way to do it, but I don't have enough knowledge yet. You can copy & paste the formula into your other year columns and use find and replace on the selected year column to update the year sheet references in the formulas.
    Attached Files Attached Files

Posting Permissions

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