+ Reply to Thread
Results 1 to 6 of 6

Sumif or countif formula update

  1. #1
    Registered User
    Join Date
    04-17-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    7

    Sumif or countif formula update

    Hello,

    I have the following formula
    SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($A$1:$G$5000,BR2:BR3,0)),ROW($A$1:$G$5000)),ROW($A$1:$G$5000))=2,1)) would like to add BR4:BR5 in there, and count only if BR2,3,4,5 are in the same row.
    For example:
    A, D, M, K,
    A, B, K, M,
    A, C, K, N,
    A, D, M, K.
    I need A, D, M, K how many times occurs. I do believe the formula can fix it with an upgrade, but just can't figure where to patch it up. If it can't work with 4 conditions, 3 will do as well:
    A, D, M,
    A, B, K,
    A, C, K,
    A, D, M.


    Thank you in advance!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Sumif or countif formula update

    Hello ZBEER and Welcome to Excel Forum.
    I need A, D, M, K how many times occurs.
    I believe an easy way to accomplish this would be to employ some helpers as modeled in the attached file.
    Note that the helpers could be moved and/or hidden for aesthetic purposes.
    Column E is populated using: =A2&B2&C2&D2
    Cell H2 is populated using: =G2&G3&G4&G5
    The count, cell J1, is made using: =COUNTIFS(E2:E5000,H2)
    If this isn't what you need, please upload a sample spreadsheet manually showing the expected outcome.
    To upload a sample spread sheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    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.

  3. #3
    Registered User
    Join Date
    04-17-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    7

    Re: Sumif or countif formula update

    Hello JeteMc,

    thank you for your response! I put an example back into the sheet you have uploaded.

    The idea would be, if a customer buys cake, tonic, melon and lemon, we can list those, and see what items, products are the most popular with those articles they are buying.

    For example, I know if it is rainy, more tonic water is needed, but what articles do they buy the most with tonic...?

    The idea would be if we could list 3-15 items, row by row, together, depending on the list of the receipt, and know by the purchased items, if birthdays are coming up, or any
    celebrations in the neighborhood by seeing statistical history, when they bought certain items the most to order for the future to come.

    No Cambridge Analytica, just PO predictions.

    Thank you very much!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Sumif or countif formula update

    Again using a helper column (M), this time populated using: =SUMPRODUCT(COUNTIFS(A11:L11,P$10:P$13))
    The count in cell P14 uses: =COUNTIFS(M11:M16,COUNTA(P10:P13))
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-17-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    7

    Angry Re: Sumif or countif formula update

    Hello JeteMc,

    thank you for the update! The solution is simple . I tend to overthink in a complicated way
    Last edited by ZBEER; 04-23-2018 at 09:55 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Sumif or countif formula update

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ 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. VLOOKUP with COUNTIF/SUMIF FORMULA
    By mommy_ynnej in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-16-2017, 03:32 PM
  2. Using a lookup & a sumif / countif in same formula
    By anthonykinsella1975 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2014, 09:45 AM
  3. Need help with Sumif and countif formula
    By mreljic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2014, 06:17 PM
  4. [SOLVED] How to set formula sumif or countif?
    By wrblee in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-24-2012, 12:42 AM
  5. countif & sumif formula with filter ?
    By unique1 in forum Excel General
    Replies: 2
    Last Post: 02-28-2012, 03:42 PM
  6. IF/THEN & a SUMIF & COUNTIF Formula
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2010, 12:32 PM
  7. Which formula to use? countif, sumif, sumproduct
    By zubee in forum Excel General
    Replies: 3
    Last Post: 09-02-2005, 04:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1