Help with COUNTIFS formula

[reneeg]

Hi,

I've created a spreadsheet that's used to collect data on the number of clients seen each month in a hearing clinic. We collect a lot of information on each client, including whether they were seen in the clinic or on outreach.

I have used the following formula, which works for the clinic clients =COUNTIFS(norrapril, "new", coapril, "clinic", ageapril, "<18", genderapril, "male". When I change the criteria to "outreach", it returns a 0. (formula=COUNTIFS(norrapril, "new", coapril, "outreach", ageapril, "<18", genderapril, "male") - it is the exact same formula, with the only difference being "outreach" instead of "clinic".

Hope that makes sense.

Any suggestion would be greatly appreciated.

Thanks
Renee

[maym]

Hi Renee

are you able to attach a sample workbook?

[reneeg]

Hi Maym

Please see attached - all formulas are in the 'stats - monthly' worksheet

Renee

[maym]

ok thanks Renee. What is the cell reference(s) of the formulas you are having trouble with?

[shukla.ankur281190]

You have additional space in criteria range F:F "Outreach " (If you wish to check then go to the Jan sheet and filter Outreach and press F2, you will see additional space)

Try

B19

Please Login or Register  to view this content.

[maym]

You have additional space in criteria range F:F "Outreach " (If you wish to check then go to the Jan sheet and filter Outreach and press F2, you will see additional space)

Try

B19

Please Login or Register  to view this content.

exactly right, found it the same time as you

Trim your values of "outreach" and should be fine.

[reneeg]

Thanks! I can see the space when filtering as suggested but I'm not sure how to trim the value of "outreach". It's taken from the list "clinicoutreach" - see references worksheet.

[maym]

was it copied initially? It looks ok in the ref worksheet. Perhaps copy it across once and then sort your data and paste it for each.

[shukla.ankur281190]

Countif and Countifs function can't handle array operation. Trim function is able to remove additional space in single value but can't remove additional space in range of value ( Only can work with designed array handle formula Like SUMPRODUCT).

Here you have additional spaces in criteria range not in criteria if you found the point of error you can easily ride off. I have spoken that you have additional spaces in criteria ranges if you can clear spaces in range then you wouldn't do change in criteria if you can't do changes in range then you would have to make sure that criteria should also look like your range.

Functions are designed very neatly if we do anything wrong the formula give us error. The error types are given below.

Row\Col	A	B	C
1	#NULL!	1	Incorrect Range Operators.
2	#DIV/0!	2	Divided By Zero.
3	#VALUE!	3	Incorrent Data Types.
4	#REF!	4	Invalid Range.
5	#NAME?	5	Formula or Range Misspelled.
6	#NUM!	6	Huge Value.
7	#N/A	7	Data is Missing.

[maym]

looking at your workbook it looks as though the data validation and list was set up after values were entered manually (or else the initial data validation had a space at the end).

You could also insert a column in each sheet and use =TRIM(cell ref) and then copy and paste over existing values. Or else a macro to change each

[reneeg]

Thank you both so much - all sorted now.

Appreciate your help.

Renee