CODEWITHSUNDEEP
functiongoogle-sheetsspreadsheet
liki kuan
liki kuan

Reputation: 1

Google Spreadsheet: Count for multiple criteria

K T

WW Data

34 1

34 3

34 4

35 2

35 5

36 1

36 0

How to count number of jobs where DATA>3 for WW=34?

I have tried on several methods but all doesn't work in Google spreadsheet. method 1: =counta(iferror(filter(T:T;TRIM(T:T)>"3";TRIM(K:K)="34")))

method 2: =ArrayFormula(sum((K:K="34")*(T:T>"3")))

method 3: =ArrayFormula(sum(if(K:K="34",if(T:T>"3")))

method 4: =count(filter(T:T;T:T>"3";K:K="34"))

method 5: =iferror(index(query(K:T,"select count(T) where T>'3' AND K='34'"),2,1),"")

But all doesn't work... Anyone can help??

Upvotes: 0

Views: 3258

Answers (2)

Alon Brontman
Alon Brontman

Reputation: 61

Pay attention to data types!

Numbers/characters that are surrounded by single/double quotation marks, such as "3" and '3' are string literals, so T>"3" compares the value in T to the string "3", not the number 3.

Therefore, when you want to compare number values, you need to make sure that both your data and the value used for comparison are numbers at the time the actual comparison takes place.

The conversion may be done implicitly by the parser for some data types, (try =NOW()*2 :), or explicitly by you, either because you know the data type is not a number, or you take some sort of precaution with regards to the column's (unknown) data type, like using the VALUE() function, (as suggested by @wchiquito).

Remarks:

  • Some formats can "break" the VALUE() function as well, making it necessary to take further precautions... but this is beyond the scope of the current discussion.
  • When you import data from other applications, a column of normal or number values may be converted to string type by either the exporting or the importing application.

Upvotes: 0

wchiquito
wchiquito

Reputation: 16551

I do not understand what you need to count, perhaps the following formulas can help you.

Columns number:

=QUERY(K:T; "SELECT COUNT(T) WHERE K = 34 AND T > 3 LABEL COUNT(T) ''")

You can also use a function like this:

=COUNTA(IFERROR(FILTER(T:T; VALUE(K:K) = 34; VALUE(T:T) > 3); ""))

Upvotes: 1

Related Questions