+ Reply to Thread
Results 1 to 16 of 16

Help with calculating unduplicated entries (across multiple columns and rows)

  1. #1
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Help with calculating unduplicated entries (across multiple columns and rows)

    Hello,

    In the attached spreadsheet, I have two tabs.

    Tab 1: "SourceData" where thousands of names are entered and dates are entered when a corresponding benefit is submitted for each person (an individual person is listed only once; the benefits can be submitted in the same month or across multiple months)

    Tab 2: "Summary" where I am looking to calculate the unduplicated number of people for whom benefits were submitted in a particular month. If one person had three benfits submitted for them in one month, I want that to count as one person for that month (for exapmle, in row 4 of "SourceData," that would count as one person in the month of January 2012). If someone had a benefit submitted during November and a benefit submitted in February, I want that to count as two people (one for the month of November 2011 and one for the month of February 2012 - example, row 5 in "SourceData")

    In the "Summary" tab I show what the totals should look like based on a manual count (this is shown in row 8).

    I have tried doing sumproduct and sumiffrequency but it seems to basically count all of the dates and give me those totals. Any idea how to do this with a formula? This is a subset of a much larger spreadsheet, so if possible, I would prefer not to have to create additional columns in the "SourceData" to pull out all possible months and years and then tabulate that.

    Any help would be greatly appreciate. Many thanks in advance.

    Best,

    Caroline
    Attached Files Attached Files
    Last edited by Hooover; 03-05-2012 at 11:44 PM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    would you be open to using a macro or userdefined function for this?
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    This is a spreadsheet I would like to send out to many different sites to keep track of this data, so if it was a macro or a userdefined function, it would be great if it was very simple and easy to use (basically so people with minimal Excel experience can use it properly). Because I don't know how to troubleshoot VBA very well, I would prefer a formula but if that's the only way and you can provide the code for me to plug in that would be great. Thanks for your help.

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,627

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Hi
    provided your data is entered in a normalized way, a Pivot Table might help ( see attached)
    With the help of a simple Data entry form, and automatic refresh, it would be user friendly enough

    Copy of BEC_Data.xlsx

  5. #5
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Thanks, Pepe. If I am reading the results correctly, that would count the total number of applications, not the unduplicated number of people for whom applications were submitted (on a monthly basis). Also, I think some of the data got eliminated...for example, FirstLast2 should have an entry in November and February, but I only see it recorded under November.

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    try this UDF

    Please Login or Register  to view this content.
    in cell C5 of summery use =CountPerRow(SourceData!$D$4:$I$9,C4)

  7. #7
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    I will try the UDF, thanks DGagnon. Do I need to define the columns if it is in a larger spreadsheet with a number of other columns that I don't want to be counted?

  8. #8
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    that UDF is built to look at all columns within the selected range. so you just need to specify the correct column range.

  9. #9
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Okay, I see where you define the range. I went into the VBA editor and placed the code, saved, went back to the Excel worksheet and put the formula into the Summary tab but I am getting a "#NAME?" error. Any idea why this is? Is there something else I need to do once I paste the code into VBA editor? Thanks so much for your help!!! Caroline

  10. #10
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    once you enter the code into a new module it should work just fine.. did you enter the code into the sheet code section, or as a module?

  11. #11
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Try also in Summary!C8,

    =SUMPRODUCT(SIGN(COUNTIF(OFFSET(SourceData!$D$3:$I$3,ROW(A$1:A$1000)-ROW(A$1),0),">="&C$4)-COUNTIF(OFFSET(SourceData!$D$3:$I$3,ROW(A$1:A$1000)-ROW(A$1),0),">"&EOMONTH(C$4,0))))

    copy down & across
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  12. #12
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Hi DGagnon, i must have entered it into sheet code section because i just went back and did it as a module and it worked great! thank you so much, really appreciate your help!

  13. #13
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Haseeb, thank you for your response as well. That formula seems to work too!

    Thanks to both of you for helping to solve my problem. I am so happy to finally have it resolved. Many thanks. Caroline

  14. #14
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Hi DGagnon and Haseeb,

    I am try to learn from both of your solutions and am also planning on using both in different capacities. I have two additional questions:

    1) If I wanted to sum by week rather than by month, how would I alter your solutions? For example, a summary for the week of March 12 through March 19.

    2) If I wanted to sum up how many people for whom two or more applications were submitted (this only needs to be done on a monthly basis), how do I alter your solutions? For example, if someone had multiple applications submitted for them in the month of January, they would count towards the total in this category. In the original attached spreadsheet, the people in rows 4, 5 and 7 would have had two or more applications submitted for them in one month - rows 4 and 7 in January and row 5 in February).

    Any help you can provide would be greatly appreciated. Thanks again for your help last week getting me started!

    -Caroline

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Quote Originally Posted by Hooover View Post
    ...If I wanted to sum up how many people for whom two or more applications were submitted...
    Try this in Summary!C8, then copy across.

    =SUMPRODUCT((COUNTIF(OFFSET(SourceData!$D$3:$I$3,ROW(A$1:A$1000)-ROW(A$1),0),">="&C$4)-COUNTIF(OFFSET(SourceData!$D$3:$I$3,ROW(A$1:A$1000)-ROW(A$1),0),">"&EOMONTH(C$4,0))>1)+0)

    For the weeks, enter Start & End dates in a cells then replace

    C$4 with Start_Cell
    EOMONTH(C$4,0) with End_date

  16. #16
    Registered User
    Join Date
    03-04-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with calculating unduplicated entries (across multiple columns and rows)

    Those both work great and were easy to implement. Thanks again for your help, Haseeb - I really appreciate your quick response. Caroline

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1