Proper use of DCOUNTA() in Google Sheets

google sheets

I'm trying to count data in my database using multiple criteria as in

=DCOUNTA(DatosBanderas;"MUNICIPIO";{{"Correcto";"=true"},{"Provincia";"=Barcelona"}})

I get Formula parse error. However, simpler queries as

 =DCOUNTA(DatosBanderas;"MUNICIPIO";{"Correcto";"=true"})

 =DCOUNTA(DatosBanderas;"MUNICIPIO";{"Provincia";"=Barcelona"})

do work. Using

=DCOUNTA(DatosBanderas;"MUNICIPIO";critrange)

with "critrange" a two by two cell range with contents "Provincia", "Correcto" in the top row and "Barcelona" and "true" in the bottom row also does work.

What am I doing wrong?

Best Answer

Try this:

=DCOUNTA($A$1:$D$7;"MUNICIPIO";{{"Correcto";"TRUE"}\{"Provincia";"Barcelona"}})

(for countries using decimal comma separators)

=DCOUNTA($A$1:$D$7,"MUNICIPIO",{{"Correcto";"TRUE"},{"Provincia";"Barcelona"}})

(for countries using decimal point notation)

The condition is the value of the cell, so it is:

"TRUE" rather than =true, and
"Barcelona"rather than "=Barcelona"

Related Solutions

Google-sheets – How to use DCOUNTA and get the month from a column instead of the entire date in the criteria

You need to use regexreplace first to remove the time part of the time stamp, and then the Text() function will properly transform the way you want.

=TEXT(REGEXREPLACE("date timestamp string","\d+:\d+:\d+",""),"MM")

Note: the timestamp part of the formula - may need to be altered depending on what exactly your string looks like, if you post an example of the string your starting with - I can modify the regex for you to fit that criteria

ALso if you share an example sheet - we can incorporate your DCOUNTA or a COUNTIF alternative

Google-sheets – How to include multiple IF statements in one cell in Google Sheets

Use lookup instead of if:

=lookup(B7,
       {"#000000","#00ff00","#ff0000","#ff9900"},
       {"Not applicable", "Read","Unread","In Progress"}
  )

Note the second parameter must be a sorted list.

Best Answer

Related Solutions

Google-sheets – How to use DCOUNTA and get the month from a column instead of the entire date in the criteria

Google-sheets – How to include multiple IF statements in one cell in Google Sheets

Related Topic