+ Reply to Thread
Results 1 to 12 of 12

Formula to Count and Return a certain message (If possible)

  1. #1
    Registered User
    Join Date
    06-24-2021
    Location
    Manchester, England
    MS-Off Ver
    MS365
    Posts
    7

    Formula to Count and Return a certain message (If possible)

    We have a sales analysis that includes a stock count of the SKUs. I wanted to have a formula that could look at the SKUs and determine if the line 2 sizes that have 0 stock then that line is labelled as 'Fragmented Stock' in the Stock Position column.

    Is there a way to do this or even something relatively similar? It's so we can easier look at lines that a fragmented to push shop by size messaging instead of manually checking everything.

    Reattached document with examples of what I'd imagine this to look like.

    Thanks
    Attached Files Attached Files
    Last edited by AliGW; 07-29-2021 at 06:51 AM. Reason: Please tag as solved - no need to edit the thread title!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to Count and Return a certain message (If possible)

    Please manually fill in 10-20 rows of expected results in column E and post the workbook again.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Formula to Count and Return a certain message (If possible)

    I agree with Ali, is it really the SKU as each appears once, is it matching displaynames you want?
    Do you mean in E2
    =IF(COUNTIFS(B:B,B2,D:D,0)>0,"Fragmented stock","")

  4. #4
    Registered User
    Join Date
    06-24-2021
    Location
    Manchester, England
    MS-Off Ver
    MS365
    Posts
    7

    Re: Formula to Count and Return a certain message (If possible)

    So essentially if it says that the sizes 5 7 and 9 have units of stock but sizes 6 and 8 have 0 units of stock column E would show the line as fragmented.

    Hope that makes sense. I've added the doc again.

  5. #5
    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,004

    Re: Formula to Count and Return a certain message (If possible)

    Other solution posted by JT
    Last edited by JohnTopley; 07-27-2021 at 10:39 AM.

  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,004

    Re: Formula to Count and Return a certain message (If possible)

    in unmerged cells

    in E2 and copy down

    =IF(COUNTIFS($B$2:$B2,$B2)=1,IF(COUNTIFS($B$2:$B7,$B2,$D$2:$D7,0),"Fragmented Stock","Stocked"),"")
    Attached Files Attached Files
    Last edited by JohnTopley; 07-27-2021 at 10:39 AM.

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Formula to Count and Return a certain message (If possible)

    =IF(COUNTIFS(B:B,B2,D:D,0)>1,"Fragmented stock","")

  8. #8
    Registered User
    Join Date
    06-24-2021
    Location
    Manchester, England
    MS-Off Ver
    MS365
    Posts
    7

    Re: Formula to Count and Return a certain message (If possible)

    Thank you!

  9. #9
    Registered User
    Join Date
    06-24-2021
    Location
    Manchester, England
    MS-Off Ver
    MS365
    Posts
    7

    Re: Formula to Count and Return a certain message (If possible)

    Thanks John!

    Is there a way to get the formula to recognise some products have more lines in them? For example if I drag to formula down on a line that has 5 SKUs to a line that has 6 one of the sizes isn't counted

  10. #10
    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,004

    Re: Formula to Count and Return a certain message (If possible)

    You obviously did not try the formula in post #6 which caters for variable length SKUs.

    The following is better ....

    =IF(COUNTIFS($B$2:B2,B2)=1,IF(COUNTIFS($B:$B,$B2,$D:$D,0),"Fragmented Stock","Stocked"),"")

  11. #11
    Registered User
    Join Date
    06-24-2021
    Location
    Manchester, England
    MS-Off Ver
    MS365
    Posts
    7

    Re: [SOLVED] Formula to Count and Return a certain message (If possible)

    Perfect, thanks John.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: [SOLVED] Formula to Count and Return a certain message (If possible)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Formula to return count of value entered
    By eberns65 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2018, 05:10 PM
  2. [SOLVED] formula to return the count of a value
    By DAVIDZZZ in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-17-2016, 09:21 PM
  3. [SOLVED] Count formula return all answers in 1 cell
    By CHillFL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2015, 10:48 AM
  4. Count, sum and return value formula
    By lar56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2015, 05:46 PM
  5. [SOLVED] VLookUP or other formula to count and return a value
    By SVTF in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2014, 12:07 PM
  6. Formula to return count, if two critera are met.
    By SeanLightfoote in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2014, 12:51 PM
  7. Formula to count editions of 999 and return a no.
    By n1kk1 in forum Excel General
    Replies: 14
    Last Post: 01-26-2011, 05:50 AM

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