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

jodiefullerton

New Member
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)

Jodie

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

arthurbr

Well-known Member
If you can adapt your source data, a Pivot Table will do the trick

jodiefullerton

New Member
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!

jodiefullerton

New Member

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

New Member

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

New Member
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!

You are welcome.

Replies
6
Views
80
Replies
12
Views
154
Replies
1
Views
114
Replies
2
Views
143
Replies
0
Views
221

1,128,015
Messages
5,628,146
Members
416,294
Latest member
McStuffins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back