+ Reply to Thread
Results 1 to 14 of 14

Using Countif statement to determine if an order contains only support material line items

  1. #1
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Using Countif statement to determine if an order contains only support material line items

    Hi all,

    I am trying to determine if in a list of orders, which orders only has a Support line item in it with no solution line items.

    I have attached sample data, but I am not sure if it is just one of those brain fart days or what. Not coming up with anything.
    Attached Files Attached Files
    Last edited by Brapp2Smokin; 10-31-2019 at 12:27 PM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Not sure where to start_Countifs?

    Hi -

    I'm not sure I fully understand what you're looking for, but the following formula yields the answer you're specifying:

    =INDEX($B$2:$B$20,MATCH("X",C2:C20,0))

    Hope that helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Not sure where to start_Countifs?

    I think the OP wants a formula to generate the X in column C.

    Pete

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Not sure where to start_Countifs?

    Hi -

    OK - Assuming what Pete says is correct, I used an array formula to create an array of Order Line Items based on Order Number. If there is at least one support line item, then it subtracts that from the total number of items in that array. That way, if there is only support items (at least 1 but there could be more) then it will put an X in column C. If there are any other line items, it leaves a blank. The formula looks like this:

    =IF(SUMPRODUCT((IF($B$2:$B$20=B2,$A$2:$A$20,0)<>0)*1)-SUMPRODUCT((IF($B$2:$B$20=B2,$A$2:$A$20,0)='Support line items'!$A$2)+(IF($B$2:$B$20=B2,$A$2:$A$20,0)='Support line items'!$A$3)+(IF($B$2:$B$20=B2,$A$2:$A$20,0)='Support line items'!$A$4))<=0,"X","")

    You could use COUNTIF rather than SUMPRODUCT. SUMPRODUCT is less efficient so for very large spreadsheets you may see a degradation of speed. However, for me, the syntax of SUMPRODUCT just makes sense. I don't care for the text strings that are often included in the COUNTIF criteria.

    Anyway, attached is your spreadsheet with the above formula (Array - so if you edit the formula, you have to press ctrl-shift-enter to engage the array functionality). I was also playing around with Order Number 404953. It has 3 entries. As a test, I changed them all to HQ Support, and an X showed up for those three. In the attached, I changed one of them back to HQ Hosting and all the X's disappeared. So, you can play with that if you want.

    Hope this helps.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Not sure where to start_Countifs?

    Here's an alternative using COUNTIFS - put this in C2:

    =IF(COUNTIFS(B:B,B2,A:A,"*support")=0,"",IF(COUNTIFS(B:B,B2,A:A,"<>"&"*support"),"","x"))

    then copy down.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Re: Not sure where to start_Countifs?

    "Hi Pete,

    Yes, that is what I am looking for. To display the X in the rows where orders have only support line items.

    I tried your =IF(COUNTIFS(B:B,B2,A:A,""*support"")=0,"""",IF(COUNTIFS(B:B,B2,A:A,""<>""&""*support""),"""",""x"")). It worked in the example data but in the full data set it is not working. I will investigate a bit more. I also noticed two things. Due to the fact the support line item names vary and ""support"" is not in all of them I believe this to be the issue. Two, I manually found an order that had two line items in it that were both support line items, so that presents a new issue where there could be an order with soley support line items, but there could be multiple lines and different types of support.

    I added a new test file where I created the issue.

    Questions, is there a way I can create a table with all the Support Names or ID's an then we reference that table?

  7. #7
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Re: Not sure where to start_Countifs?

    I believe that is actually what @loginjmor's formula resolves, but I am still working to see if I can get that one to work. As of now, I am not getting it to return x's on the already manually identified order.

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Not sure where to start_Countifs?

    Title much better. Thank you
    Last edited by Pepe Le Mokko; 11-01-2019 at 03:52 AM.

  9. #9
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Re: Not sure where to start_Countifs?

    Updated title. Please advise if further corrections are required.

  10. #10
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Using Countif statement to determine if an order contains only support material line i

    Hi -

    Yes, the formula I proposed reads from a list of Supports and compares them to the array for each Order Number. So that would be expandable with additional SUMPRODUCTS. It's not nearly as elegant as Pete's. If the list gets too long, we may need to reconsider the solution. Can you post a larger data set with the problem(s) identified? Maybe we can figure it out.

  11. #11
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Re: Using Countif statement to determine if an order contains only support material line i

    Yes, so the real data set is about 20k rows.

    The support line item descriptions are 15. instead of doing text description I can do Support line item ID's. Would that be easier?


    I have added a new file.

  12. #12
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Using Countif statement to determine if an order contains only support material line i

    Hi -

    Sorry, work got in my way. Attached is your updated spreadsheet. I had to add a helper column to determine if a Order Line Item was considered support. If it found the Order LIne Item in your list of support items, it simply returns the row it occurs in. If it doesn't find that item in support, then it returns a -1.

    Then I just updated the formula to create an array of + and - values and then use sumproduct to see how many items by Order Number were negative. If there are zero negative items, then that order number solely has support. If there is even one negative number, then there is more than just support.

    The first formula that test to see if and Order Line Item is in your list of support items. It's a simple MATCH lookup:

    Please Login or Register  to view this content.
    If the MATCH returns an ERROR, that means that Order Line Item is not on the list, so we use the IFERROR to return a -1. Otherwise, it just returns the row number as a positive number. The advantage of this approach is you can add items in the future and it will be simple to adjust the testing formula.

    The formula for displaying or not displaying an X looks like this:

    Please Login or Register  to view this content.
    Again, this is an array formula, so ctrl-shift-enter to engage that functionality.

    Hope this helps.
    Last edited by loginjmor; 11-01-2019 at 03:57 PM.

  13. #13
    Registered User
    Join Date
    03-12-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    365
    Posts
    41

    Re: Using Countif statement to determine if an order contains only support material line i

    Hi Loginjmor. This appears to return the correct results. Thank you!

  14. #14
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Using Countif statement to determine if an order contains only support material line i

    Glad I could help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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