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

jodiefullerton

New Member
Joined
Sep 3, 2020
Messages
11
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
1610113688395.png

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
 

Some videos you may like

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
Joined
Dec 8, 2006
Messages
2,201
Office Version
  1. 2010
If you can adapt your source data, a Pivot Table will do the trick
 

jodiefullerton

New Member
Joined
Sep 3, 2020
Messages
11
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Sep 3, 2020
Messages
11
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 3, 2020
Messages
11
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 3, 2020
Messages
11
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top