Google Sheets SUM + CountIfs formula not working

[jemrisin]

I'm using Google Sheets and am trying to get this formula to work to give the me the following count:
Count when Column T = Kenneth AND Column U = (Pending OR Contacted) AND Column W has a date that falls between the dates shown in B14 and B15.
This is what I have so far:
=sum(countifs(Users!$T:$T,"Kenneth",Users!$U:$U,{"Pending","Contacted"},Users!$W:$W,">"&$B14,Users!$W:$W,"<="&$B15))
This is giving me the correct count for "Pending" alone but it is ignoring all the "Contacted" rows so somehow it is not recognizing that OR operator.
Any help would be greatly appreciated!

 

[Weazel]

I'm not sure what the differences are with the countifs between google sheets and excel but I couldn't get it to work.

you can try....

=SUMPRODUCT((T2:T3000="kenneth")*(U2:U3000={"pending","contacted"})*(W2:W3000>B14)*(W2:W3000<=C14))

which seems to work ok, though I'd try not to use full column references ... or maybe it doesn't matter with google sheets.

 

[Aladin Akyurek]

01. Google Sheets seems to understand:

=SUM({1,2}) >> 3

02. Since there is no F9 or Evaluate Formula equivalent, it's hard to inspect what the following delivers:

=COUNTIFS(A:A,{"x","y"})

where column A houses 3 x's and 2 y's. This displays one would say 3 of the expected {3,2}.

03. However...

=SUM(COUNTIFS(A:A,{"x","y"}))

>> 3

This forces one to conclude that COUNTIFS of Google does not admit, unlike Excel, an array (or range) as criteria.

Given above, try...

=SUMPRODUCT(Users!T:T="Kenneth",ISNUMBER(MATCH(Users!U:U,{"pending","contacted"},0)),Users!W:W>=B14,Users!W:W<=B15)

Yes, it's surprising to see that Google's SUMPRODUCT doesn't require coercing.

 

[jemrisin]

This approach fixed the problem thanks Weazel! The final formula I used was:

=SUMPRODUCT((Users!$T:$T="Kenneth")*(Users!$U:$U={"Contacted","Pending"})*(Users!$W:$W>$B14)*(Users!$W:$W<=$B15))

 

[Weazel]

you're welcome, glad it worked for you