Forumlas with multiple conditions

The concept is very simple (and I can do it in SQL) but having a lot of problems trying to create the formula in excel.

I need to count the number of rows in another sheet where the status is certain values and one of the date fields falls between certain dates.

I can do each part individually, but can not create a combined formula and I'm not sure if this is something I am doing wrong now or something that can not be done.

My formula for counting the number of rows based on a date (this checks another sheet in the book but the principal is the same)
=COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18)

My formula for counting the number of occurances of particular statues.
=COUNTIFS('TEST DATA2'!F:F,"On Hold Customer")+COUNTIFS('TEST DATA2'!F:F,"On Hold Other")


so in pseudo code terms
IF the date column on page TEST DATA2 is > one date value and <= another date value
AND
Order Status coumn on TEST DATA2 is one of a number of values
COUNT THIS ROW.


Can this be done?


Thanks

Hi Marvin, I'm using MS Office Professional Plus 2013, but will update my profile page.

Thanks

Thank you, thank you, thank you!!!

You have literally saved my sanity on this.

Originally Posted by Pauleyb

You almost had it. I think this should work:
=COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Customer")+COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold Other")

It may be able to be simplified if your only instances of strings that begin with "On Hold" are those two:
=COUNTIFS('TEST DATA'!A:A,">"&B19,'TEST DATA'!A:A,"<="&B18,'TEST DATA2'!F:F,"On Hold*")