CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacountif
Kristena Jones
Kristena Jones

Reputation: 1

Countifs with year and other reference cells with text

Looking for a formula for 3 criteria in google sheets to count if all are TRUE:

Number of entries that equal a specific region
Number of entries that say "Yes"
Number of entries that have a date submitted in 2024

Column A is the Regions: Eastern, Western, Southern

Column B is Accepted, with Yes or No in the cells

Column C is Date Submitted (date format is MM/DD/YY)

I would like to know how many Accepted entries there were for the Western Region in 2024.

I tried combinations of Countifs() formulas, but I am not sure how to get the date equals the year 2024.

Google Sheets Image: enter image description here

Upvotes: 0

Views: 153

Answers (2)

Harun24hr
Harun24hr

Reputation: 37050

Here are few other approaches.

=SUMPRODUCT((A2:A="Southern")*(B2:B="Yes")*(C2:C>=DATE(2024,1,1))*(C2:C<=DATE(2024,12,31)))
=SUMPRODUCT(--(FILTER(A2:A,A2:A="Southern",B2:B="Yes",C2:C>=DATE(2024,1,1),C2:C<=DATE(2024,12,31))<>""))
=QUERY(A2:C,"select count(A) where A='Southern' and B='Yes' and C>= date '2024-01-01' and C<= date '2024-12-31' label count(A) ''")

enter image description here

Upvotes: 1

Saddles
Saddles

Reputation: 1859

Try this:

=COUNTIFS(A2:A, "Western", B2:B, "Yes", ARRAYFORMULA(YEAR(C2:C)), 2024)

OUTPUT

OUTPUT

You may also try this:

=COUNTIFS(A2:A, "Western", B2:B, "Yes", C2:C, ">=" & DATE(2024,1,1), C2:C, "<=" & DATE(2024,12,31))

OUTPUT

OUTPUT

In the example, I did not see any Yes on Western, so I changed for testing purposes.

REFERENCE

Upvotes: 1

Related Questions