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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If you can adapt your source data, a Pivot Table will do the trick
 
Upvote 0
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!
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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!
 
Upvote 0
You are welcome.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top