6 Replies Latest reply on Sep 16, 2015 5:19 PM by EdwinMerced

# Countif Unique values across multiple fields

I have 10 multiple fields (named A1 through A10). These fields only accept values 1, 2 3 or 4 from a Value List. I need to count the amounts of 1s, 2s, 3s, and 4s across all fields (A1-A10). For example: 4-1s, 2-2s, 2-3s and 2-4s

I have used =COUNTIF in Excel to do this but do not see that function in Fielmaker.

Thanks for any help.

• ###### 1. Re: Countif Unique values across multiple fields

EdwinMerced wrote:

I need to count the amounts of 1s, 2s, 3s, and 4s across all fields (A1-A10). For example: 4-1s, 2-2s, 2-3s and 2-4s

Let (

theList = List ( A1 ; A2 ; A3 … A10 ) ;

List (

ValueCount ( FilterValues ( theList ; 1 ) ) & " 1s" ;

ValueCount ( FilterValues ( theList ; 2 ) ) & " 2s" ;

ValueCount ( FilterValues ( theList ; 3 ) ) & " 3s" ;

ValueCount ( FilterValues ( theList ; 4 ) ) & " 4s"

)

)

though you very likely should use a child “A” table with 10 records per parent, rather than 10 fields.

• ###### 2. Re: Countif Unique values across multiple fields

I used the following in a calculation field I called COUNTIF_FOR_A:

Let (theList = List ( A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20;A21;A22;A23;A24;A25;A26;A27;A28;A29 );

List (

ValueCount ( FilterValues ( theList ; 1 ) ) & "1" ;

ValueCount ( FilterValues ( theList ; 2 ) ) & "2" ;

ValueCount ( FilterValues ( theList ; 3 ) ) & "3" ;

ValueCount ( FilterValues ( theList ; 4 ) ) & "4"

)

)

But when I place the field in the form it returns a strange number (9110210304). I need it to tell me how many "1" were chosen, how many "2" where chose, how many "3" and how many "4" where chosen.

• ###### 3. Re: Countif Unique values across multiple fields

EdwinMerced wrote:

it returns a strange number (9110210304).

1. That's not possible, because List() will insert CRs; so the actual result should be:

91

102

103

04

2. if you had used (as suggested)

ValueCount ( FilterValues ( theList ; 1 ) ) & "x 1"

you would get

9x 1

10x 2

10x 3

0x 4

which the result you're after.

Alternatively, use e.g.

"1: " & ValueCount ( FilterValues ( theList ; 1 ) )

which gives you

1: 9

2: 10

3: 10

4 : 0

Please consider my suggestion regarding the use of a related table; your current non-normalized structure will cause (more) issues down the road.

• ###### 4. Re: Countif Unique values across multiple fields

Thank you elrost.

I am new to FM. How do individually extract those four numbers? (as I want to calculate percentages for each one).

• ###### 5. Re: Countif Unique values across multiple fields

EdwinMerced wrote:

How do individually extract those four numbers? (as I want to calculate percentages for each one).

Well, it would really be better to use a related table and a sorted set, because the aggregate field type (summary fields) allow you to analyse (and do other things with) a set of records much more conveniently than with a group of fields.

EdwinMerced wrote:

I am new to FM.

So wouldn't it be a good idea to read the Help or other FM documentation, or attend a training? Learning about data normalization and creating reports and summaries would be helpful.

That being said: you could create two number fields, each with 4 repetitions, as

field: cNumberCounter =

Let ( [

i = Get (CalculationRepetitionNumber) ;

theList = List (Extend(A1);Extend(A2);Extend(A3);Extend(A4);Extend(A5);Extend(A6);Extend(A7);Extend(A8);Extend(A9);Extend(A10);Extend(A11);Extend(A12);Extend(A13) // etc. )

] ;

ValueCount ( FilterValues ( theList ; i ) )

)

and

field: cPercentages =

Let ( [

i = Get (CalculationRepetitionNumber) ;

all = Count (Extend(A1);Extend(A2);Extend(A3);Extend(A4);Extend(A5);Extend(A6);Extend(A7);Extend(A8);Extend(A9);Extend(A10);Extend(A11);Extend(A12);Extend(A13) // etc. )

] ;

cNumberCounter[i] / all * 100

)

Be aware that the second field references the first one.

Easiest to use text objects as labels to show what these values stand for.

• ###### 6. Re: Countif Unique values across multiple fields

Thank you for your suggestion elrost. I have already begun taking some virtual training and reading on Filemaker.

Also, thank you for your solution.