Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 at 12:27

1 Answer 1

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

share|improve this answer
    
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 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 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 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 at 16:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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