+ Reply to Thread
Results 1 to 29 of 29

COUNTIFS based on multiple criteria that tracks changes in two columns

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    COUNTIFS based on multiple criteria that tracks changes in two columns

    Hello,
    See picture below:

    COUNTIFS.png

    I wish to create a COUNTIFS formula that functions as follows:

    a. Counts how many times values in Column D & E reappear.
    b. Resets to 0 if Criteria X changes. (Changed marked with yellow.)
    c. Resets to 1 if Criteria Y changes. (Changes marked with blue.)
    d. Resets to 0 if both criteria changes.

    Multiple Name+Thing combinations with their own criteria X and Y exist. This is why this must be included in the formula's criteria. I've hidden some sample rows with Anne+Shirt instead of Bob+Car as an example of this. Bob+Thing is independent of Anne+Shirt.

    Additional info:
    Criteria X goes from 0 to ∞.
    Criteria Y only goes from 0 to 1.
    All changes in Criteria X and Y are in consecutive increasing increments of 1. I.e. 1, 2, 3, 4, 5, etc...


    I've been able to make the formula work for all points except that for point (c.), where it should reset to 1 if Criteria Y changes with:
    In N3: =COUNTIFS($D$3:D3;D3; $E$3:E3;E3; $F$3:F3;F3; $G$3:G3;G3)-IF(F3>0;1;0)

    (The IF(F3>0;1;0) is to make the formula reset to 0, and start at 1 at N3 (since no changes happen at N3, like 0 to 1, 1 to 2, etc..).

    See attached Excel sheet.
    It's easier to understand when comparing the source data to a visual representation of the desired end result.

    Thanks

    EDIT: See last post by AliGW on page 2 for the solution.
    Last edited by 27POP27; 05-28-2019 at 08:19 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,612

    Re: COUNTIFS that tracks changes in two columns

    Please try at N2
    =COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-(F3>0)+(G3<>G2)*(D3=D2)

  3. #3
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS that tracks changes in two columns

    Quote Originally Posted by Bo_Ry View Post
    Please try at N2
    =COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-(F3>0)+(G3<>G2)*(D3=D2)
    Hi, thank you for your response, but this doesn't work with multiple Name+Thing values. See picture below where I've un-hidden some rows in this sheet with Anne+Shirt.

    In the actual sheet I'll use this formula for there are hundreds of Name+Thing combinations. Like for instance Bob+Shirt, Bob+Whatever, and Michal+Whatever, etc.etc...
    Because of this I'd try avoid consecutive relations like (G3<>G2)*(D3=D2).

    This problem is already solved in the first part of my attempt formula: =COUNTIFS($D$3:$D3;$D3;$E$3:$E3;$E3 ...

    COUNTIFS2.png
    Last edited by 27POP27; 05-22-2019 at 01:52 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,881

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Testing Bo_Ry's formula after the rows containing 'Anne' and 'Shirt' are unhidden, it seems to work. The only row in which it does not return the value listed in column L is row 30 and looking at point d. in post 1 (Resets to 0 if both criteria changes) it seems as if Bo_Ry's formula is again correct.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Hi, thanks for your response.

    When I say 'When a criteria changes' I mean that a value change happen at a per-row basis.
    For Anne: Criteria X changes from 0 to 1 at row 22. Then Criteria Y changes from 0 to 1 at row 30.
    Therefore only Criteria Y changed at row 30, and therefore N30 should be 1 according to point c.
    Last edited by 27POP27; 05-27-2019 at 03:15 AM.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,612

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    How about N3
    =COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-(F3>0)+(G3<>G2)-ISTEXT(G2)

  7. #7
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    This leads to the formula stop counting between N31 and N32, thus both N31 and N32 (for Bob) is 2. N31 should be 1.
    Column L shows the desired results. See picture below.

    I'd recommend avoiding any sequential dependencies that refer to either the previous or next row for calculations, explained in my post before the previous.

    27.05.19 2.png
    Last edited by 27POP27; 05-27-2019 at 08:33 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    These large images are just clutter - please just upload the workbook. If you must post visuals, please cut them down to just a view of the relevant area (e.g. rows 26 to 36) so that they take up less room. The thread is getting difficult to follow. Thanks!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,612

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    I apologize about not avoiding sequential and still get the error.

    I still use sequential because I have no idea how to track change from 0 to 1 or 1 to 0, without comparing with sequential cell.
    This is my last try
    N2 =IF(G2<>G3;1;COUNTIFS($D$3:$D3;$D3;$E$3:$E3;$E3;$F$3:$F3;$F3;$G$3:$G3;$G3)-(F3>0))

    Good luck and hope that you get others help.
    Last edited by Bo_Ry; 05-27-2019 at 08:40 AM.

  10. #10
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Quote Originally Posted by Bo_Ry View Post
    N2 =IF(G2<>G3;1;COUNTIFS($D$3:$D3;$D3;$E$3:$E3;$E3;$F$3:$F3;$F3;$G$3:$G3;$G3)-(F3>0))
    Hi, thank you for your help but this won't work if I for instance add two Criteria Y changes in a row. In the attached sheet I've now made Criteria Y count 1 from G31, then N31 becomes 0, when it should be 1. See picture.

    28.05.19 2.png

    I still use sequential because I have no idea how to track change from 0 to 1 or 1 to 0, without comparing with sequential cell.
    Just for clarification, all changes increase by increments of 1, never decrease. See additional info in original post:
    Additional info:
    Criteria X goes from 0 to ∞.
    Criteria Y only goes from 0 to 1.
    All changes in Criteria X and Y are in consecutive increasing increments of 1. I.e. 1, 2, 3, 4, 5, etc...
    Last edited by 27POP27; 05-28-2019 at 01:50 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    You said that if both criteria change, 0 should be returned. Why should N30 and N31 be 1? This breaks the logic you gave at the outset:

    a. Counts how many times values in Column D & E reappear.
    b. Resets to 0 if Criteria X changes. (Changed marked with yellow.)
    c. Resets to 1 if Criteria Y changes. (Changes marked with blue.)
    d. Resets to 0 if both criteria changes.
    Please explain again and in detail what the logic should be.

  12. #12
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Quote Originally Posted by AliGW View Post
    You said that if both criteria change, 0 should be returned. Why should N30 and N31 be 1? This breaks the logic you gave at the outset:

    Please explain again and in detail what the logic should be.
    Name+Thing is it's own criteria. Bob+Car is independent of Anne+Shirt. This is included in the first part of my sample formula =COUNTIFS($D$3:D3;D3; $E$3:E3;E3; ...
    Multiple Name+Thing combinations with their own criteria X and Y exist. This is why this must be included in the formula's criteria. I've hidden some sample rows with Anne+Shirt instead of Bob+Car as an example of this.
    For Anne+Shirt, only Criteria Y changes from 0 to 1 at G30. Criteria X stays at 1 from F22 to F30. Therefore N30 should be 1 according to c.
    For Bob+Car, only criteria Y changes from 0 to 1 at G31. Criteria X stays at 4 from F29 to F31. Therefore N31 should be 1 acfording to c.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Ah, I see. OK - I'll have a think.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Try this:

    =IFERROR(IF(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)<G3,1,COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-(F3>0)),1)

  15. #15
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Hi, thanks for you answer.

    This solves point b) c) and d), but misses point a) at N32 and N33.
    28.05.19 3.png

    The thing that's so annoying with this problem is that the original formula =COUNTIFS($D$3:D3;D3; $E$3:E3;E3; $F$3:F3;F3; $G$3:G3;G3)-IF(F3>0;1;0)
    already solved 98% of the problem. It works for every case, no matter how complex any additional Name+Thing combinations or Criteria X and Y changes are, except that it misses point c) and resets to 0 when Criteria Y changes (and thus all counting after that happens)
    Last edited by 27POP27; 05-28-2019 at 04:03 AM.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    I am sure you can tweak it to get what you want. Just 2% to go ... Let us know when you crack it.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Just noticed an anomaly. You said this:

    Criteria Y only goes from 0 to 1.
    So why are cells G34 onwards 2? And what triggers a change in Y?
    Last edited by AliGW; 05-28-2019 at 05:44 AM.

  18. #18
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Quote Originally Posted by AliGW View Post
    Just noticed an anomaly. You said this:

    So why are cells G34 onwards 2? And what triggers a change in Y?
    My apologies. I added two Criteria Y changes in row to demonstrate why consecutive relations likely won't work, and forgot to delete the second.
    Criteria Y should only go from 0 to maximum 1.

    Attached Excel sheet is updated to show this:

  19. #19
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    For clarification:

    If anyone finds the simplified sheet being used in this thread to be be too arbitrary and wish further details, see attached sheet of the actual purpose I'll use this formula for.
    If not please ignore this post.

    This is an inspection log where we note down which parts we receive that are defective.
    Column G counts consecutive positive inspections, and resets to 0 if we find a defect part. The column G formula is what I wish to figure out.
    The catch here, is that we sometimes find defect parts in production that we missed in inspections. The last detected defect is listed in column F.
    When this happens, I wish the counting to start at 1, because a Positive inspection is often completed after that defect part was discovered. (See row 2411 and 2475. Between these inspections a defective part (column F) was discovered.)
    Column H = Criteria X, and Column L = Criteria Y in the other simplified workbook basically. (Column K and L is just me messing around with formulas. K and L can be deleted).
    Attached Files Attached Files
    Last edited by 27POP27; 05-28-2019 at 06:34 AM.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    I don't understand your expected results in rows 34 and 35. Please explain why these are 1.

    In your latest sample (NOT the real workbook), in Q3 copied down:

    =IFERROR(IF(AND(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$F$2:$F2)=F3,LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)=G3),COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-MIN(1,COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3,$Q$2:Q2,0)),IF(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)<>G3,1,0)),1)

    This is not correct in rows 34 and 35 based on your desired outcome which I simply do not understand in terms of the logic.
    Last edited by AliGW; 05-28-2019 at 07:02 AM.

  21. #21
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Hi,

    Should've also updated these when I changed that Criteria Y placement. Row 34 and 35 should be 4 and 5 of course.

    Ill try your formula.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Where is the 'correct' sheet?

    See column Q:

    Excel 2016 (Windows) 32 bit
    L
    M
    N
    O
    P
    Q
    2
    Desired end result: My attmept:
    3
    1
    1
    1
    4
    2
    2
    2
    5
    3
    3
    3
    6
    4
    4
    4
    7
    5
    5
    5
    8
    1
    1
    1
    9
    6
    6
    6
    10
    7
    7
    7
    11
    8
    8
    8
    12
    2
    2
    2
    13
    0
    0
    0
    14
    1
    1
    1
    15
    2
    2
    2
    16
    3
    3
    3
    17
    4
    4
    4
    18
    5
    5
    5
    19
    6
    6
    6
    20
    7
    7
    7
    21
    8
    8
    8
    22
    0
    0
    0
    23
    9
    9
    9
    24
    10
    10
    10
    25
    0
    0
    0
    26
    1
    1
    1
    27
    0
    0
    0
    28
    1
    1
    1
    29
    0
    0
    0
    30
    1
    1
    1
    31
    1
    1
    1
    32
    2
    1
    ?This should be 2
    2
    33
    3
    2
    ?This should be 3
    3
    34
    1
    3
    4
    35
    1
    4
    5
    36
    0
    0
    0
    37
    1
    1
    1
    38
    2
    2
    2
    Sheet: Sheet1

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Let me know if it's worked for you - trying to work it out has given me a thumping headache!

  24. #24
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Hi,

    Wow, that formula is a little to digest.
    That works great though for the sheet I've given you.
    Only thing is if I mess with the sheet and make both Criteria X and Y change at the same time at row 31, the formula becomes 1.

    I think this whole problem could've been written better by myself from the start, and include a example of every point a) b) c) and d) with multiple Name+Thing combinations.
    Sorry for the headache:PP Getting one myself.


    I think this thread is getting close to impossible to follow now, due to small mistakes and misunderstandings here and there.
    Do you think it's better if this thread is deleted and re-made? I will rewrite the thread and include a better example that takes every point into consideration.
    I could also just update the original post and my latest post to include a better sheet though. I won't be able to do that today though.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    No, absolutely not! We do not allow more than one thread on each issue - you must continue here.

    Anyone wishing to help will need to respond here. I have to confess, the various errors you have made along the way have not helped ...

    I might have a look at the latest file once I have recovered from the last one.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Try this:

    =IFERROR(IF(AND(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$F$2:$F2)=F3,LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)=G3),COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3)-MIN(1,COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3,$F$3:$F3,$F3,$G$3:$G3,$G3,$Q$2:Q2,0)),IF(AND(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$F$2:$F2)<>F3,LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)<>G3),0,IF(LOOKUP(2,1/($D$2:$D2=D3)*($E$2:$E2=E3),$G$2:$G2)<>G3,1,0))),1)
    Attached Files Attached Files
    Last edited by AliGW; 05-28-2019 at 07:59 AM.

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    I've amended the formula to do what you want. See post #26 where I have attached your file.

  28. #28
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Hey,

    Seems like it works on all points a) b) c) d)
    Thanks a lot AliGW. I'll try this formula out on my original 3000+ row sheet and see how it works in the coming weeks. I'll try to modify it if I find some irregularities.

    I'll mark this thread as solved, since your question has answered what this thread asked about.

    Thanks again. +1

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,081

    Re: COUNTIFS based on multiple criteria that tracks changes in two columns

    Thank heavens for that!

    I was about to throw in the towel.

+ 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. tracks/managing outlook inbox
    By baluraipur in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-20-2015, 06:40 AM
  2. Counting Tracks Played Over a Period
    By Nelix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2014, 06:12 PM
  3. Modifying a Macro that Tracks Changes
    By leooclaws in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2014, 12:19 PM
  4. [SOLVED] How to create a formula that keep tracks of new value.
    By fadu4u in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-31-2014, 10:06 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  6. Tracks Changes in workbook not shared?
    By Cameronl in forum Excel General
    Replies: 2
    Last Post: 01-06-2009, 07:10 AM
  7. Is there a spreadsheet that tracks document in an office?
    By Frederick in forum Excel General
    Replies: 1
    Last Post: 04-10-2006, 06:35 PM

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