Forumla Assistance: Countif cell x says "text" and cell y is ">0" "<5" Multiple criteria

[jodiefullerton]

Hello, I really hope you are can help. Not going to lie been trying to figure this one out for the best part of 2 hours and have now given up.

I have logged on for a while so I have breached any rules please let me know.

So here goes. column B I have criterias that all have different targets.
Enquiries - 5 working day
St1 Complaints - 5 Working Days
St2 Complaints - 20 Working Day s
SPSO - 5 Working days

In column Y I have totalled how many working days it has taken from receiving the enquiry / complaint to closing.

My management want it broken down into for each criteria.

Therefore I am looking for a formula that will only count the Enquiries in column b if they are >0 <6, >5 <=10 etc so on. This is what I have done.. trying to work it but its not working...
=COUNTIF(Master!$D$5:$D$2000,"Enquiry")+COUNTIF(Master!$Y$5:$Y$2000,6)+COUNTIF(Master!$Y$5:$Y$2000,7)+COUNTIF(Master!$Y$5:$Y$5000,8)+COUNTIF(Master!$Y$5:$Y$2000,9)+COUNTIF(Master!$Y$5:$Y$2000,10)

Thanks so much in advance.

Jodie

 

[arthurbr]

If you can adapt your source data, a Pivot Table will do the trick

 

[jodiefullerton]

thank you, I am not sure how to do that, I can get the pivot to show me the enquiries, s1 complaints etc broken down and how many but not sure how to get it to show how many were between 1-5 day etc. Sorry!

 

[Joe4]

Try using a COUNTIFS formula, which allows for multiple criteria in one formula.

This shows you an example of how to do that: Excel formula: Count cells between two numbers | Exceljet

 

[jodiefullerton]

Hi Joe, i have been doing this for the past two hour and can't seem to find the correct formula. I have tried countifs but I don't know how to incorporate it all as it seems to only count the numbers and not take into consideration the name "enquiry"
As I only want it to count all numbers >0 =<5 that have the criteria of "enquiry"

 

[Joe4]

Hi Joe, i have been doing this for the past two hour and can't seem to find the correct formula. I have tried countifs but I don't know how to incorporate it all as it seems to only count the numbers and not take into consideration the name "enquiry"
As I only want it to count all numbers >0 =<5 that have the criteria of "enquiry"

Did you take a look at the example I referenced? That shows you how to get the data range part.
Note that you can keep adding ranges and conditions to COUNTIFS (it is not limited to just two).
So once you have the number range part working, just then add another range/condition to check the "enquiry" criteria part.

This link shows you the structure of COUNTIFS a little clearer: MS Excel: How to use the COUNTIFS Function (WS)
Note that there are a "pair" of arguments for each condition, range and criteria.
So your structure is going to be:
=COUNTIFS(range1,criteria1,range2,criteria2,range3,criteria3)
If you can figure out how to do any one condition in COUNTIF, you should have no problems incorporating it into COUNTIFS.

 

[jodiefullerton]

I had a look and I have tried so many different versions, i am not sure if its because I have my drops downs where you can select multiple options. This is what i have and its giving me 0 but I know that I have 2 that should be returned.

=IF(A57="","",COUNTIFS(Master!$D$5:$D$2000,"*"&A57&"*",Master!$Y$5:$Y$2000,">0",Master!$Y$5:$Y$2000,">=5"))

A57 is the word Enquiry,

 

[jodiefullerton]

Scrap that, omg I put > that < less than 5... im away to hit my head off a brickwall for the foreseeable. thanks for your help!

 

[Joe4]

You are welcome.