CODEWITHSUNDEEP
excel-formulagoogle-sheets
Julz
Julz

Reputation: 69

Google Spreadsheet Counta Two string conditions in separate rows

I am trying to achieve a counta of two string conditions.

There is a database with: Columns C2:C which contains a string of languages. Columns F2:F which contains a string of TRUE / FALSE.

   C         F
German     TRUE
German     TRUE
German     FALSE
German     FALSE
French     FALSE
French     TRUE
French     TRUE

etc.

The answer I'm looking for: (IF DASHBOARD!B12 = "German", AND F:F = "TRUE" , then the answer would be 2)

I have already tried:

=COUNTA(IF(AND(C:C=DASHBOARD!B12,F:F="TRUE"),1,0))
//This gives me the answer of 1?

=COUNTA(IFERROR( FILTER( F2:F , SEARCH( "TRUE" , F2:F ) ) ) )
// This gives me the correct answer of 89

=COUNTA(IFERROR( FILTER( C2:C , SEARCH( DASHBOARD!B12 , C2:C ) ) ) )
//This gives me the correct answer of 37

How do you merge the last two expressions so that the counta will ONLY count those where column F = TRUE and column C = DASHBOARD!B12?

I have already tried =SUMPRODUCT() and =ARRAYFORMULA() to no avail, there must be something that is going wrong with such a simple expression...

Upvotes: 0

Views: 523

Answers (1)

timchen
timchen

Reputation: 388

I would suggest you use true() and false() function instead of string "true", "false". It is better to put in function if(). You can use more condition in filter(),like: filter(C:F,C:C="French",C:C="TRUE")

I have made a example sheetsheet for your reference. Please check below. https://docs.google.com/spreadsheet/ccc?key=0AhDkbjKh37REdGloc2NFbDlXeGNrSm1TTHhyVFFWelE

I believe the data in C:D is what you want.

Upvotes: 1

Related Questions