Results 1 to 4 of 4

Thread: Need a Report Calculating Across Multiple Columns from Different Worksheets

  1. #1

    Need a Report Calculating Across Multiple Columns from Different Worksheets

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

    In my workbook, I have four worksheets that I am trying to work through the following problem. Those sheets are "Stakeholder Roster", "County Roles", "Agency List" and "Roles By Agency Count".

    "Stakeholder Roster" contains column with about 1100 employees within 30 Agencies. There are then 20 columns for which each row/stakeholder can be assign multiple roles from a drop-down list of ~65 roles.

    In the sheet "Roles By Agency Count", the Agency list is in column B; the 65 roles are across row 2. On this worksheet, I want to have the intersection of an Agency and a Role tell me how many times a role was assigned within a particular Agency in the "Stakeholder Roster" sheet. The challenge is that in the "Stakeholder Roster" sheet, the same role may appear in various columns (i.e., role X is not always listed down one column for all users; user 1 may have role # in column L while user 2 may have role # assigned in column P)..

    I will gladly share the file with anyone who would like to see it for more context.

  2. #2
    He Mcgonma - can you pls share the file.. Thanks

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    I think this is what you are requiring. (works in 2010)
    Bit of a long way around but it gets the job done.

  4. #4
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Middle Wisconsin
    If you add the countifs along with an ifelse formula, you could draw it out to work - another long way around it, but have used many times and works well (yeah, very similar to your idea vinod). You could also add it all to a Power Pivot data model, then calculate it all out there - have the data return into a table and it would work.

Posting Permissions

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