Google Spreadsheet COUNTIF formula equivalent with

2020-08-17 17:15发布

问题:

I have a Google Docs spreadsheet with a few columns. In column D I have values Man OR Woman. In column G I have values Yes OR No. What I would like to to do is to count how many times MAN will answer with YES. So there are two criterias 1.Man and 2.Yes

I have this formula which only counts values in cells D2 and G2. I would like to count values from D2 to D500 and from G2 to G500.

=IF(AND('Revision: 2'!D2="Man",'Revision: 2'!G2="Yes"),1,0)

How I can do that?

Thank you!

回答1:

Google spreadsheet does not support COUNTIFS function ... however following is one of several formulas you can use for what you want to do ...

=ArrayFormula( SUM( ( D2:D500 = "Man" ) * ( G2:G500 = "Yes" ) ) )


回答2:

In the new version of Google Spreadsheets COUNTIFS is already built in. You can use it for multiple criteria.

Sample Usage:

COUNTIFS(A1:A10, ">20", B1:B10, "<30")
COUNTIFS(A7:A24, ">6", B7:B24, "<"&DATE(1969,7,20))
COUNTIFS(B8:B27, ">" & B12, C8:C27, "<" & C13, D8:D27, “<>10”)