Problems with COUNTIFS

[mahat]

1. I have three columns of data like age group, sex and income range. I am trying to count the number of male/female persons within each income range for a specified age group. The formula used is simple like :=COUNTIFS('FileName '!cell_range1,"< income_1", 'filename="" '!cell_range1,="" "="">=>Income_2", 'FileName'! cell_range_1,"
'FileName!'cell_range1, "< Income_1", 'FileName"!cell_range1, "Female")< income_1", 'filename="" '!cell_range1,"female")

2 This works fine in most cases but for each calculation of, say 6 categories, the total number of instances and the breakup female/Male does not tally with what is found by Home--> Find --> Income range as graded by A, B etc or found out by COUNTIF or manually. Unfortunately with 500 cells in each column this is tedious. Also the total fails by 1 to 4 often.

3. What can be the reason for this strange result pattern?

4.Does attaching a data table help?
Thanks.

 

[Aladin Akyurek]

Is 'Table: Class Characteristic Value' (post #10) the output that you need? If so, why don't you include the values e.g. in the 5000 and 6000 range?

 

[mahat]

Yes the table indicates the needed output. I would like to know the number of cells where the value is not known since it is required for analysis. If I club this with 5000-6000, the classification suffers. Alternately I have to create a range that is specific to this case. How can I do it without other values losing their identity.

Further it can be seen from the next posting of modified table that even when this ''offending" cell is eliminated the problem persists.

This is happening with almost all the tables I have created in this context, sometimes with more than one value showing error. It is true even when a numeric classification of the range is adopted.

 

[Aladin Akyurek]

Yes the table indicates the needed output. I would like to know the number of cells where the value is not known since it is required for analysis. If I club this with 5000-6000, the classification suffers. Alternately I have to create a range that is specific to this case. How can I do it without other values losing their identity.

Further it can be seen from the next posting of modified table that even when this ''offending" cell is eliminated the problem persists.

This is happening with almost all the tables I have created in this context, sometimes with more than one value showing error. It is true even when a numeric classification of the range is adopted.

I re-cap my query: Don't we need the following record at row 448...

448 M 5500

in the relevant count?

 

[Joe4]

Can you post the EXACT formulas you are using?

The formula in your first post looks messed up and also contains a bunch of cell/range references (note the link I provided in my first reply which gives tips on how to keep formulas with less than signs from getting messed up when posting - use the "Preview" button before posting to make sure it will work!).

I need to know your exact formulas, and the values in any cell references that it uses.

 

[mahat]

Can you post the EXACT formulas you are using?

The formula in your first post looks messed up and also contains a bunch of cell/range references (note the link I provided in my first reply which gives tips on how to keep formulas with less than signs from getting messed up when posting - use the "Preview" button before posting to make sure it will work!).

I need to know your exact formulas, and the values in any cell references that it uses.

1. = COUNTIFS (I6:I447," < 5999", H6:H447,"F")
2. = COUNTIFS (I6:I447, " >5999",I6:I447, " < 10999", H6:H447,"F")
3. = COUNTIFS (I6:I447," < 16000", I6:I447, " > 10999", H6:H447,"F")
4. = COUNTIFS (I6:I447,"< 21000", I6:I447, " > = 16000", H6:H447, "F")
5. = COUNTIFS (I6:I447," > 20999",I6:I447, "< 25999", H6:H447,"F")
6. = COUNTIFS(I6:I447," < 30999", I6:I447," > 25999", H6:H447,"F")
7. = COUNTIFS (I6:I447, " > 30999", H6:H447,"F")
The above are used for the seven rows in the third column under 'Female'; They repeat for the fourth column under 'Male' the last criteria changed to "M". Column 'I' refers to column heading "Income" and Column 'H' refers to column heading 'Gender'.
The "VALUE!"is drawn from a column which reads like = M1+N1, M & N being numeric and one or both of them being not known is entered with a character (typically '$'), in which case the total is indicated as an error.

 

[mahat]

I re-cap my query: Don't we need the following record at row 448...

448 M 5500

in the relevant count?

I am sorry I do not get this quite right. There is no row 448 in the table... The table ends with row 447.. Can you please explain what you mean by this? Thanks

 

[Joe4]

I think the problem you may be having is a typo in one of your formulas.
I created the same situation you laid out in post #10 and using the formulas you just posted in #15.

For level # 4 (characteric value "1600-20000", Class "M"), I return 33 records, not 32. That accounts for the difference of one you are seeing.

Here is the formula I have:

=COUNTIFS(I6:I447,"<21000",I6:I447,">=16000",H6:H447,"M")

Check this formula on your spreadsheet to make sure you do not have a typo in there.

If not, you may have a data issue then (i.e. one of you Class value may have a space before/after it).
Try using the Length function on each row to ensure this isn't happening, i.e.
=LEN(H6)
and copy down for all rows.
If any return anything other than 1, it is data issue.