Excel - Formula question

[Joanne]

Right I've got a spreadsheet full of data. I want to be able to count various things in it, eg/ girls on phase 4, SEN boys, Boys on target.

 

I've got the formula =COUNTIFS(blah blah blah) and I can get what I want BUT... how do I make it so that someone could press buttons to build the formula?

 

eg/ buttons such as Boys, Girls, FSM, On Track and you can select any of them and then it will give you a number of your selections.

 

I'm guessing some sort of macro?

 

Thanks in advance.

[pcstru]

I may not be understanding so ...

 

Use data validation to allow users to pick the value in a cell from a list (I usually have a tab called lookups which contains the lists). Your countif's then compare to the value the user selects.

[Joanne]

The selection they choose would be the criteria, but not the criteria range - which is what I'm struggling with, because there are quite a few yes/no true/false columns.

[pcstru]

The selection they choose would be the criteria, but not the criteria range - which is what I'm struggling with, because there are quite a few yes/no true/false columns.

The range needs to not change, just the criteria.

 

You probably need to build an intermediate sheet which expands the data into a form which contains a column which contains a compound of the criterias. So if each row has 4 attributes which you can select from (Gender, FSM, On Track, Ethnicity) the new column will contain a concatenation of the criteria, using an IF formula to check if the attribute is required.

 

So lets say we have 4 tabs; the first contains the user input using dropdowns in A1, A2, A3 and A4, from which you can select an attribute or a blank. A5 contains the answer : how many rows match the criteria. The second tab contains your data and the fourth contains the lookups for the dropdown. Tab 3 now contains data+, it has one more column which looks at what is selected in A1, A2 etc and if appropriate, concatenates the relevant attribute for the row into the content of the cell. Then your countif can do the same with it's criteria (create a compound one from the dropdowns) and then hopefully you see that we have a criteria and columns to match against and if rows don't have the criteria they will not be counted so no need to alter the range.

[pcstru]

Sorry, I think that is as clear as mud! I'll try and knock up an example but not sure how quickly I can do that. If you had an anonomised example you could send (don't need many rows to show how it works), that would help.

[theeggmaster]

I know this isn't using formulas, but would a pivot table do what you need?

[Joanne]

I know this isn't using formulas, but would a pivot table do what you need?

 

I initially thought this but the data is yes/no true/false m/f and SEN/n/a so it struggles to make head nor tales of it!!

 

- - - Updated - - -

 

Sorry, I think that is as clear as mud! I'll try and knock up an example but not sure how quickly I can do that. If you had an anonomised example you could send (don't need many rows to show how it works), that would help.

 

Yeah I'll remove the names and send it over... please can you pm me your address>?

[Bedders]

Quickly throwing this out there without a massive amount of thought:

 

Would it not be possible to create a VBA script to go with the dropdown - so the teacher selected from the drop-down what they were searching for, then you input that into the formula as pcstru said.

 

Behind that you could have a bit of VBA that ran when the dropdown box was updated, which retrieved the value in the drop-down and ran through a SELECT statement to output to a custom function giving the range...

 

 

I've just realised how over-complicated that sounds.

[bobsmith]

The most reliable way to do this kind of thing is to use the AutoFilter with a macro, it's pretty simple once you've got your head round the basic.

 

What it will do is copy only the data that meets all the criteria to a new array and then you have your simple count tell you how many rows have met all the criteria.

 

It works with virtually unlimited conditions, including null criteria if they want to skip some of the drop downs.

 

this kind of thing: VBA Code To Filter Data Then Copy & Paste Into Range

[andy_b]

I have attached a quick demo - it relies on INDIRECT() and named ranges, but I suspect it could get out of hand pretty quickly..

Countifs.xlsx

[pcstru]

I have attached a quick demo - it relies on INDIRECT() and named ranges, but I suspect it could get out of hand pretty quickly..

It is a clever use of Indirect and named ranges. I suspect you are right it would get unwieldy quite quickly ... but ... mmm.

Edited February 3, 2017 by pcstru