+ Reply to Thread
Results 1 to 11 of 11

Help with counting using (and relating) data from multiple sheets

  1. 03-16-2017, 05:37 AM #1
    aralmeida
    aralmeida is offline
    Registered User
    Join Date
    03-14-2017
    Location
    Portugal
    MS-Off Ver
    2007
    Posts
    5

    Help with counting using (and relating) data from multiple sheets

    Hello,
    i'm a newbie in the forum so i hope i follow all the post rules.
    I'm trying to count how many people participated in activities in a given year.
    I have 3 sheets:
    Activities: Contains a unique ID, the date, description of tue activity and where it happened.
    Atttended: Contains the name of the people who attended the activities. Column name is the activity ID and matches Activities ID column.
    Dashboard: Well i just want to enter the year and get the number of activities done in that year and the number of participants in that activity.

    The problem: how to create a formula in Dashboard that counts all "Yes" from activities organizer in the given year? Is it possible?

    Thank you.
    Reply With QuoteRegister To Reply
  2. 03-16-2017, 05:40 AM #2
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,906

    Re: Help with counting using (and relating) data from multiple sheets

    Hi, bem vindo ao Forum
    Welkom,
    Do you have a sample file you could attach? Replace private data en email address with dummy data.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  3. 03-16-2017, 06:24 AM #3
    aralmeida
    aralmeida is offline
    Registered User
    Join Date
    03-14-2017
    Location
    Portugal
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with counting using (and relating) data from multiple sheets

    Hello,
    sorry i forgot to attach it. Here it is.
    Attached Files Attached Files
    Last edited by aralmeida; 03-16-2017 at 07:44 AM.
    Reply With QuoteRegister To Reply
  4. 03-16-2017, 07:49 AM #4
    aralmeida
    aralmeida is offline
    Registered User
    Join Date
    03-14-2017
    Location
    Portugal
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with counting using (and relating) data from multiple sheets

    Mental algorithm seens simple:
    select all rows from Activities that match the year and count "sim" (yes in Portuguese) from all the columns whose Headers are in those rows in the [ID] column.

    But this seems to be harder than i thought in Excel.
    Any sugestions?
    Reply With QuoteRegister To Reply
  5. 03-16-2017, 07:50 AM #5
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,906

    Re: Help with counting using (and relating) data from multiple sheets

    I'll see what I can do for you.
    Reply With QuoteRegister To Reply
  6. 03-16-2017, 07:55 AM #6
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,906

    Re: Help with counting using (and relating) data from multiple sheets

    You have to use CountIFs and compare ranges, maybe even Index and so.
    A Macro would be easier though but it can be dona with a formula, howvere with macros you have to remeber tu check all in lower or upper case letters since you use NÃO, Não and não as well as in the Sim combinations
    Reply With QuoteRegister To Reply
  7. 03-16-2017, 08:14 AM #7
    aralmeida
    aralmeida is offline
    Registered User
    Join Date
    03-14-2017
    Location
    Portugal
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with counting using (and relating) data from multiple sheets

    Can you explain what you mean with compare ranges?
    Thank you
    Reply With QuoteRegister To Reply
  8. 03-16-2017, 09:56 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,605

    Re: Help with counting using (and relating) data from multiple sheets

    This proposed solution adds a helper row (B8:R8) under Tabela2 populated by a formula which correlates the event ID's to dates:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The formula in Dashboard G9, which counts the number of 'sims' for the year given in F5 is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  9. 03-17-2017, 03:27 AM #9
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,906

    Re: Help with counting using (and relating) data from multiple sheets

    @JeteMc: nice solution, however I would that opt to start TAbela2 on Row4 and the helper row now (B8:R8) to be place above Tabela2 Row 3, that way it will not accidentally be deleted id data is added to Tabela2 in an incorrect way.
    Reply With QuoteRegister To Reply
  10. 03-20-2017, 07:46 AM #10
    aralmeida
    aralmeida is offline
    Registered User
    Join Date
    03-14-2017
    Location
    Portugal
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with counting using (and relating) data from multiple sheets

    That is a nice solution.
    There is a "small" problem the user keeps record of activities by adding columns and the user has to manually copy the the formula every time.
    Is there a way ti avoid/minimize this?
    (I confessed I'm still trying to obtain all columns whose header is in ID and has a date of year, as a learning exercise)
    Reply With QuoteRegister To Reply
  11. 03-20-2017, 08:55 AM #11
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,605

    Re: Help with counting using (and relating) data from multiple sheets

    As Keebellah states, place the helper row above the table and copied over as far as you anticipate needing it. Cells containing the formula that are above empty cells will display #N/A, however you could wrap the formula in the IFERROR function to prevent that.
    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Urgent Please Help! Look up a value and return multiple instances and data relating to it
    By OzBizConcierge in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-22-2013, 11:19 PM
  2. Copying a formula relating to different sheets...
    By Finalfrontier1976 in forum Excel General
    Replies: 4
    Last Post: 01-06-2012, 11:30 AM
  3. Relating multiple columns in two spread sheets
    By Cemt5462 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2011, 05:05 PM
  4. Counting Data From Multiple Sheets
    By w3dgie in forum Excel General
    Replies: 11
    Last Post: 02-22-2011, 01:57 PM
  5. Counting data on multiple sheets using countif
    By nickelcell1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2010, 03:05 PM
  6. counting occurences of values relating to date range
    By Newport Count in forum Excel General
    Replies: 11
    Last Post: 10-24-2009, 06:53 AM
  7. Relating date between sheets in same workbook
    By honestsoul in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-10-2009, 12:31 PM
  8. Fix a formula when relating Sheets
    By ezahrai in forum Excel General
    Replies: 4
    Last Post: 01-14-2006, 05:34 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1