Counting a range of values, in a range of cells as well as a different range of values in another cell/range of cells

[Excel_User_10k]

Hi All,

First time poster, long time supporter. Formula's requiring more than basic functions drive me crazy trying to fix as MS are absolutely useless in helping to solve the error in their own program!

Anyway, I have a row of values in the form of percentages for KPI's and I want to count the amount that are in certain ranges (i.e. Green/100%, Amber/80-90%, etc). Most KPI's are straight forward 100% = hit. And using Countsifs works perfectly for that range. However, I am also trying to get it to include the values of other cells where, for example, Green=30% into the count. And this is where I am getting stuck. I am stating what the value of the cell needs to be, as I have with the first set, and it accepts the formula in that it doesn't cause an error message. But I get the "#VALUE!" in the desired destination as the result. I feel like I am really close but I am clearly missing something. Please can someone help with this?

Here is where I have got to with the formula so far to count 'Ambers' that will hopefully illustrate what I am trying to achieve (green highlights how much of the formula DOES work so far)...

=COUNTIFS(E4:I4,">=0.89",E4:I4,"<=0.999",K4,">=0.1",K4,"<=0.149",L4,">=0.23",L4,"<=0.329")

Thank you.

 

[Peter_SSs]

Welcome to the MrExcel board!

I think that your question would probably be a lot clearer if we had a small set of dummy sample data and the expected result(s)
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Excel_User_10k]

Hi Peter,

Thank you. I cannot do a screenshot as it contains confidential information but here is the best I can do with some of the information redacted or cropped out. I hope it is enough for you to understand what I was trying to explain and what I am trying to achieve though.

Appreciate the prompt response.

 

[Peter_SSs]

I cannot do a screenshot as it contains confidential information

With XL2BB you can provide any part of a worksheet and/or hide any rows/columns with sensitive information. From what you have shown in your image, there appears to be nothing confidential so that same part of the sheet could be shown with XL2BB and helpers would not have to manually type all that out.

Also, does that screen shot include the expected results as requested? If so, can you explain one or two of them? If not, please provide some expected results with explanation.

In relation to the #VALUE! error that you are getting, that is because in COUNTIFS the syntax is ...
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], [criteria_range4, criteria4]…)
.. and in that syntax, every criteria_range must have the same number of rows and columns as each other. That is not the case with your attempted formula in post 1 where the first two criteria ranges have 1 row and 4 columns but the next two ranges have 1 row and 1 column.

 

[Excel_User_10k]

Hi Peter,

Ok, so it isn't that I have constructed the formula incorrectly. It just wouldn't work for what I want it to do. Well it is a lot more messing about but the other option was to add up the three sections individually on a separate hidden sheet and then add them all up automatically in the desired cell. But as there are so many rows to do this and it is there are 12mths of sheets, I imagine that could get messy.

And to answer your other question: the other's are all just entered manually. Just thought I would try making it automated. Sounds like I it may be easier to keep to manual haha.

 

[Excel_User_10k]

Ok, so perhaps I can get help with another area of this particular file.

I am trying to get it to work out the fraction of Greens out of all. So I want it to add all of the Greens, Ambers, Reds and Blues. And then say how many of those are Green (for instance, 2/10 Green). I cannot work out the mathematical term for this to put into the Formula.

Any ideas please?

 

[Peter_SSs]

How are the colours applied? (Manually, Conditional Formatting, vba code)

Could we have a small set of dummy data and the expected results with XL2BB?