Below is a small example database that I have created for reference.

http://i.stack.imgur.com/8qODH.png

What function can I use to only display users that have 0 'Data Used', 'Minutes Used' and all 'Amount Used' rows must be no more than 1 for that user?

In this particular example, only the information for 'Steven Jones' would be displayed.

  • This question lacks details about effort you've put into this so far. What have you tried? Have you tried using a pivot table? – CharlieRB Sep 4 '14 at 12:27

You could add a helper column onto the end of your results and filter on that to show/hide as you need.

This function works by counting how many rows fail your criteria - ie how many rows have either more than 0 Data or Minutes Used, or more than 1 amount used.

If any rows fail any of your criteria then the if() statement returns "hide". If there are no matching rows at all, then it's ok to include them in your results so the if() returns "include".

 =IF(
     COUNTIFS($A$2:$A$14,A2
             ,$E$2:$E$14,">0"
             ,$F$2:$F$14,">0"
             ,$G$2:$G$14,">1"
             )>0
    ,"Hide"
    ,"Include"
    )

enter image description here

  • Unfortunately there's an error here. A complete row must have values other than 0 & 1 respectively to hide the particular user. – B-B. Sep 4 '14 at 13:16
  • The logic applied here follows the criteria laid out in your question: "only display users that have 0 'Data Used', 'Minutes Used' and all 'Amount Used' rows must be no more than 1 **for that user?** In this particular example, only the information for 'Steven Jones' would be displayed." – Andi Mohr Sep 4 '14 at 13:45
  • If I've misunderstood the criteria please edit your question to make it very clear what the requirements are. – Andi Mohr Sep 4 '14 at 13:46
  • This exactly solves the asked question and provides the same result as the example. You should tell where this behaves not like you want it to ? – Falco Sep 4 '14 at 16:07

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.