Filter variables.

[edsashley]

I have a formula that is counting from a table of data. =COUNTIFS(D:D,IF($N$15="","*",$N$15),F:F,"<>e-learning*",F:F,"*",H:H,"*",H:H,"<>status")

This works fine as I enter a variable into cell N15 it only counts data from that location for example Cardiff south

Is it possible to change this formula so that it will accept two variables ie I define a group called Cardiff but it actually inputs the variable Cardiff north and Cardiff south.

 

[myall_blues]

You can do:

=COUNTIF(D:D,"*"&N15&"*",F:F,"<>e-learning*",H:H,"<>status")

You don't need the additional IF statement.

Out of interest, is there a specific reason the last part of your statement has

F:F,"<>e-learning*",F:F,"*",H:H,"*",H:H,"<>status"

The F:F,"*" part says matching anything in F, and then the F:F,"<>e-learning*" part says only count things in F not equal to e-learning*. You don't need the F:F,"*" part as it is not doing anything - COUNTIFS counts everything unless you tell it not to.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Cardiff South
2				Cardiff South			10
3				Cardiff South
4				Cardiff South
5				Cardiff South
6				Cardiff North
7				Cardiff North
8				Cardiff North
9				Cardiff North
10				Cardiff North
11				Lilliput
12				Lilliput
13				Lilliput
14				Lilliput
15				Lilliput										Cardiff
16
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNTIF(D:D,"*"&N15&"*")

 

[edsashley]

If I remember correctly then the additional if statement was to deal with an empty N15 cell ie location not specified. This would then return data for all locations. The F:F, “*” part and the same with the H:H,”*” was like an error check for example if data was in f but not h then it wouldn’t count and vice versa.

 

[myall_blues]

OK. At least in the first instance for N15 empty the IF is no longer required as it now incorporates a wildcard whether empty or not.

 

[edsashley]

This has not worked as expected. I have uploaded a picture of the template I have made.

There is a drop down box which is populated with the locations as entered into column ‘O’ in ‘N15(my variable).

As can be seen in column’O’ I have inputted Cardiff, then with a large space (so only Cardiff can be seen in the drop down) I have entered Cardiff north, Cardiff south.

I had hoped that by selecting Cardiff from the drop down the variable Cardiff north and Cardiff south would be entered as two separate variables into the formula and show data for both locations. This is not the case.

 

[myall_blues]

I had hoped that by selecting Cardiff from the drop down the variable Cardiff north and Cardiff south would be entered as two separate variables into the formula and show data for both locations. This is not the case.

That wasn't the brief.

Your original post included a COUNTIFS formula you wanted modified so that entering Cardiff would include variants such as Cardiff North, Cardiff South etc. The count the solution produces (in G2 above) does just that - in the example with Cardiff in N15 it produces a count of 10, being a count of all the 'Cardiff*' values.

I also don't understand what column O has to do with it, since that wasn't mentioned and the value in N15 does not reflect the value you have entered in O17.

If you explain your requirements more clearly then we can probably derive a solution.

 

[edsashley]

I have created the table as a template that can be copied and pasted into another sheet. This has been done as the data is exported from software on the web into excel format. The locations found in this exported data include Liverpool, Manchester, Newcastle, Edinburgh etc. this is fine because they are their own locations however when it comes to Cardiff they have for some reason been split into the two sites, north and south but they are in the same area of responsibility so I wanted to group these. Column ‘O’ just contains the locations of the data that will be imported and its sole purpose is to populate the drop down box located in cell N15