+ Reply to Thread
Results 1 to 16 of 16

Count with changes in multiple criteria

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Count with changes in multiple criteria

    Hello,

    I am trying to add a .1 and increase by .1 each time there is a change in two columns. Attached is an example where the first column would signal a change in a company and the second column would signal a change within one company's description for a transaction. I have a column showing the result I am trying to get. I have attempted a number of formulas and they aren't working how I would like them to. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    Re: Count with changes in multiple criteria

    Should D6 ( and hence D7) not be suffix of 0.2 as there has been a change in Remark for the same Invoice Number?

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Count with changes in multiple criteria

    I would like the .1 to increment upward for each change in remark field and reset to .1 for each change in invoice field

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    Re: Count with changes in multiple criteria

    4180706-2003 02/02-03/04 #AUGM1 E 4180706-2003.1 row 6
    4180706-2003 01/01-02/01 #92564300 W 4180706-2003.1 row 7
    4180706-2003 01/01-02/01 #92564300 W 4180706-2003.1
    4180706-2003 01/07-02/05 #101542300 W 4180706-2003.2
    4180706-2003 01/07-02/05 #101542300 W 4180706-2003.2
    4180706-2003 01/07-02/05 #101566300 W 4180706-2003.3

    It is the Same invoice

  5. #5
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Count with changes in multiple criteria

    yes it is the same invoice however there is a secondary invoice in the remark field. I am trying to separate with an incremental .1 for the change in the remark field with a reset to .1 whenever the invoice field changes. The example I input in the example column shows what I am trying to accomplish.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    Re: Count with changes in multiple criteria

    Rows 3/4: same Invoice number but different remarks so suffix of .1 and ,2
    row 5 NEw invoice number , suffix .1
    row 6 same invoice, new remarks suffix .2
    row 7 same invoice, same remark suffix ,2 (as per row 6)
    row 8 same invoice new sremark, suffix .3

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Count with changes in multiple criteria

    Would suggest 2 options:
    1) XXX.1 (1 digit)
    =A3&"."&SUMPRODUCT(IFERROR(1/COUNTIFS($A$3:A3,A3,$B$3:B3,$B$3:B3),""))

    2) XXX.01 ~ XXX.10 (2 digits)
    =A3&"."&TEXT(SUMPRODUCT(IFERROR(1/COUNTIFS($A$3:A3,A3,$B$3:B3,$B$3:B3),"")),"00")

    Both are array formula,
    **are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Attached Files Attached Files
    Quang PT

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Count with changes in multiple criteria

    In C3 then copy down.

    =IF(A3<>A2,A3&".1",IF(B3=B2,D2,A3&"."&MID(D2,FIND(".",D2)+1,3)+1))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Count with changes in multiple criteria

    Quote Originally Posted by kvsrinivasamurthy View Post
    In C3 then copy down.

    =IF(A3<>A2,A3&".1",IF(B3=B2,D2,A3&"."&MID(D2,FIND(".",D2)+1,3)+1))
    You mean in D3?

    @amartin575
    Both solutions work, assuming that each invoice number is placed togerther, and Remarks is placed together too.

    Not like this:
    Invoice 1 Remark 1
    Invoice 1 Remark 2
    Invoice 1 Remark 1 (repeat again)

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Count with changes in multiple criteria

    Quote Originally Posted by bebo021999 View Post
    You mean in D3?

    @amartin575
    Bo
    It is C3 only not D3
    Attached Files Attached Files

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count with changes in multiple criteria

    Please try at D3

    =A3&"."&IF(A3=A2,MID(D2,FIND(".",D2)+1,9)+(B3<>B2),1)
    Attached Files Attached Files

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Count with changes in multiple criteria

    Quote Originally Posted by kvsrinivasamurthy View Post
    It is C3 only not D3
    In your attachment #10, your formula link to column D that might not exist

    Copy to D3 then it works.

    Anyway, good job done with MID.

  13. #13
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Count with changes in multiple criteria

    yes that is correct John.

  14. #14
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Count with changes in multiple criteria

    @kvsrinivasamurthy. It looks like you are keying off of the example column. That won't be there and the increments aren't working in all cases. It is in some of them.

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Count with changes in multiple criteria

    Quote Originally Posted by amartin575 View Post
    @kvsrinivasamurthy. It looks like you are keying off of the example column. That won't be there and the increments aren't working in all cases. It is in some of them.
    Sorry for the mistake. I have coorrected.
    In C3 then copy down.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Count with changes in multiple criteria

    Thanks everyone. The formulas worked nicely and as expected. Much appreciated!

+ 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. [SOLVED] Formula to count matches between multiple columns on different sheets w/ multiple criteria
    By RICK JAMES in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-05-2019, 03:28 PM
  2. Count with multiple criteria (Date, Time and Other Criteria)
    By Naren_Kumar in forum Excel General
    Replies: 6
    Last Post: 06-25-2018, 05:53 PM
  3. Count cells if any criteria are met, not multiple criteria
    By jesstaylor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2014, 12:50 PM
  4. Count number of Rows with multiple criteria (multiple valued cells)
    By garog in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-09-2014, 01:33 PM
  5. Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's
    By akaushik25 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 08:58 AM
  6. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  7. Count Multiple Entries, Return Multiple Rows, Based On 3 Criteria
    By gtj_global in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2008, 06:40 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