CountIFS with and operator or SUMPRODUCT?

I am trying to do an AND between 2 conditions which work with a count.I want a result of something as pasted below

(COUNTIFS(F33:F83,"<12/14/2015",F33:F83,">12/11/2015") and (COUNTIFS(G3:G53,"\Release 2\Sprint 2")))

So the first count is anything between the 2 dates and the next is the count of anything which matches "\Release 2\Sprint 2"

I tried SUMPRODUCT but not able to get it right.
=SUMPRODUCT(--(F33:F83="<12/14/2015",F33:F83=">12/11/2015"),--(G3:G53="\Release 2\Sprint 2")))

And I definitely know that this is not doing an AND operation but I tried nevertheless
=SUM(COUNTIFS(F33:F83,"<12/28/2015",F33:F83,">12/25/2015")+(COUNTIFS(G3:G53,"\Release 2\Sprint 3")))


Help would be really appreciated

Hi,

Excel can't handle "and/or" in different ranges within COUNTIFS or SUMPRODUCT.
So to get to the right answer your data must be aligned over the same row numbers.
Either by shifting G3:G53 down so it aligns with F33:F83 or the other way around.

Hope this helps.

Originally Posted by joris moerings

So to get to the right answer your data must be aligned over the same row numbers.
Either by shifting G3:G53 down so it aligns with F33:F83 or the other way around.

I didn't understand this part, Joris.

Ankur, That didn't work.

It will be better if you can upload a sample spreadsheet with desired result.

Go to advance and click on attachment link and attach the workbook

Thanks
Ankur

1. I want a count of dates which fall between 12/14/2015 and 12/11/2015 in Created Date column.
2. I want a count of items matching the string "\Release 2\Sprint 2" in Iteration Path.
Want to 'AND' both of above