# Forumlas with multiple conditions

1. ## 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

2. ## Re: Forumlas with multiple conditions

Hi eSmith and welcome to the forum,

We need to know what version of Excel you are using. You can add that to your Profile page. The answers we give may vary based on the version you are using. See
http://office.microsoft.com/en-us/ex...010342341.aspx
for why and see if it helps.

3. ## Re: Forumlas with multiple conditions

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

Thanks

4. ## Re: Forumlas with multiple conditions

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*")

5. ## Re: Forumlas with multiple conditions

Hi,

If you have the latest version of Excel you can do "Between" dates in a pivot table. I've created a simple table of dates, sku and amt. Then I did a pivot table with the data and selected Between Dates and only a few of the SKUs. See if this helps with your problem. Learn more about Pivots?
http://www.contextures.com/excel-piv...ters-date.html

6. ## Re: Forumlas with multiple conditions

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*")

7. ## Re: Forumlas with multiple conditions

this seems to work
=SUM(COUNTIFS('test data'!A:A,">"&B19,'test data'!A:A,"<="&B18,'test data2'!F:F,{"On Hold Customer","On Hold Other"}))

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1