+ Reply to Thread
Results 1 to 15 of 15

Sum, Countifs, match Etc.

  1. #1
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Sum, Countifs, match Etc.

    I really didn't know how to title this as it seems like I need to nest a few functions together ha. I am needing to count the number of times each unique number is found in a column, based on a location (found in another column) based on a month and year (found in two additional columns)

    I tried figuring this out on my own and didn't get far. below is what I have and it does not work to say the least. I know it doesn't include everything I'm asking for but as I said, I was trying to start somewhere.

    Please Login or Register  to view this content.
    The following are the columns of interest:

    A = Location

    H = Column to count unique numbers

    M = Month

    N = year

    If I can get this knocked out, it will lead me to another question...but we can cross that bridge when we get there ha.

    any and all help would be greatly appreciated.

    thanks

    BR

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,241

    Re: Sum, Countifs, match Etc.

    You could probably use a helper column to test for unique entries using COUNTIF(range,cel)=1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    after some more Googling, I was able to find this;

    Please Login or Register  to view this content.
    this has me half was there I think. it is telling me number of unique entries correctly.

    Now I just need to count how many times each entry found. then narrow that down with countifs maybe to get it down to location, month, and year

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,241

    Re: Sum, Countifs, match Etc.

    Can you upload a small sample workbook, along with some sample answers?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,241

    Re: Sum, Countifs, match Etc.

    Perhaps you can adapt the ranges in this for what you need?

    A
    B
    C
    D
    E
    22
    Name Data Duplicates CountUnique CountUniqueB
    23
    A
    1
    1
    9
    3
    24
    B
    2
    2
    25
    B
    2
    0
    26
    A
    3
    2
    27
    A
    3
    0
    28
    C
    4
    1
    29
    B
    5
    4
    30
    B
    5
    0
    31
    C
    5
    0
    32
    A
    5
    0
    33
    A
    6
    1
    34
    C
    7
    2
    35
    B
    7
    0
    36
    C
    8
    1
    37
    A
    9
    1

    D23=SUMPRODUCT(--(FREQUENCY(B23:B37,B23:B37)>0))
    E23=SUM(--(FREQUENCY(IF(A23:A37="B",B23:B37),B23:B37)>0))
    E23 is an ARRAY formula, entered using CSE

  6. #6
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    Hopefully this will help. I tried the formulas you provided but couldn't get them to work. should the (--( be in there or is that a place holder for something I am supposed to enter?

    Count Example.xlsx

    thanks

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Sum, Countifs, match Etc.

    Hi.

    Can you just clarify some of your expected results?

    You have a value of 1 for Alice/Oct-13, though I cannot see any records which match these criteria.

    You have a value of 3 for Dickinson/Apr-14, though I only see 2 (unique) records which match these criteria.

    You have a blank for Alice/Oct-14, though I can see 1 unique record which matches these criteria.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    Quote Originally Posted by XOR LX View Post
    Hi.

    Can you just clarify some of your expected results?

    You have a value of 1 for Alice/Oct-13, though I cannot see any records which match these criteria.

    You have a value of 3 for Dickinson/Apr-14, though I only see 2 (unique) records which match these criteria.

    You have a blank for Alice/Oct-14, though I can see 1 unique record which matches these criteria.

    Regards
    Your right. I had oct - 13 and 14 confused. as for Dickinson I must have over counted

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Sum, Countifs, match Etc.

    You could use this array formula** in B2:

    =SUM(IF(FREQUENCY(IF(Master!$A$2:$A$22=Results!$A2,IF(Master!$M$2:$M$22=MONTH(Results!B$1),IF(Master!$N$2:$N$22=YEAR(Results!B$1),MATCH(Master!$H$2:$H$22,Master!$H$2:$H$22,0)))),ROW(Master!$A$2:$A$22)-MIN(ROW(Master!$A$2:$A$22))+1),1))

    and copy down and to the right.

    If you really don't want zeroes to appear you could mask them using either a Custom Cell Format or some Conditional Formatting.

    Or, if you want to do it in the formula itself, perhaps:

    =IFERROR(1/(1/(SUM(IF(FREQUENCY(IF(Master!$A$2:$A$22=Results!$A2,IF(Master!$M$2:$M$22=MONTH(Results!B$1),IF(Master!$N$2:$N$22=YEAR(Results!B$1),MATCH(Master!$H$2:$H$22,Master!$H$2:$H$22,0)))),ROW(Master!$A$2:$A$22)-MIN(ROW(Master!$A$2:$A$22))+1),1)))),"")

    which at least avoids a lengthy, resource-heavy repetition of the whole clause (i.e. IF(formula=0,"",formula), but you should be warned that this double-reciprocation trick can sometimes lead to rounding errors.

    Probably best to go with one of the masking options if you really can't stand zeroes in there.

    Regards
    Last edited by XOR LX; 11-12-2014 at 11:40 AM.

  10. #10
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    Nice, that works for me, thanks for the help.

    on another note if you could help, now I need to find how many times a value is found in the well name column for a given month/year/district.

  11. #11
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    Nice, that works for me, thanks for the help.

    on another note if you could help, now I need to find how many times a value is found in the well name column for a given month/year/district.

  12. #12
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Sum, Countifs, match Etc.

    If you could re-post your attachment with some sort of results tabled included for this new task, then I'm sure I'd be able to help.

    Regards

  13. #13
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    this should help.

    Also, this being a sample or my actual data, the rows to be used go up to 10735 (including the header) when I tried changing the rows in the previous example to this number it returned a 0?

    just thought I would throw that in there ha. thanks again for all your help

    Count Example.xlsx

    thanks
    BD

  14. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Sum, Countifs, match Etc.

    Quote Originally Posted by bdrilling33 View Post
    this should help.
    Sorry - is this not the same attachment as previously? Which part am I looking at in here now? Did you set up a table ready to house your new results?

    Quote Originally Posted by bdrilling33 View Post
    Also, this being a sample or my actual data, the rows to be used go up to 10735 (including the header) when I tried changing the rows in the previous example to this number it returned a 0?
    Did you remember to re-commit them as array formulas after making those changes?

    Regards

  15. #15
    Forum Contributor
    Join Date
    05-30-2014
    Posts
    112

    Re: Sum, Countifs, match Etc.

    well I thought I did, I must have made the changes and not save it or something. Nonetheless, we was able to get what I needed using a series of countifs and come cheater cells to achieve what I needed. Thanks again for all your help, it was much appreciated and will be used in the future.

    thanks
    Brice

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs Index Match
    By Keelin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2014, 08:38 AM
  2. COUNTIFS and MATCH functions
    By PJR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2014, 07:45 PM
  3. Countifs Until Criteria Is Match
    By Zipmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2013, 12:34 PM
  4. VBA: Help with countifs / Match
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-29-2012, 01:42 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM

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