Menu
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!
232
The COUNTIFS() function in Google Sheets can be used to count the number of rows in a spreadsheet that meet multiple criteria. This function uses the following syntax: COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, …)
The Excel formula to countif multiple criteria[1] is =countifs(). The “s” on the end makes it plural and therefore implies that there are multiple criteria (2 or more). In the examples and illustrations below in this tutorial will show you exactly how to use COUNTIFS in Excel.
In Google Sheets, you can't use the formula as above. Multiple criteria in Countifs in the same column is not possible or recommended in Google Sheets. Actually, it's not correct. We can include multiple conditions from the same column (OR criteria in the same column) and also from a different column in Countifs.
#3 Count Cells with Multiple Criteria – Between Two DatesTo get a count of values between two values, we need to use multiple criteria in the COUNTIF function. We can do this using two methods – One single COUNTIFS function or two COUNTIF functions.
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" ) ) )
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”)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
