# Excel formula with date and criteria

This is a discussion on Excel formula with date and criteria within the Excel Questions forums, part of the Question Forums category; Hi, I have tried almost all the posts on this, and none of them are working....I am about to manual ...

1. ## Excel formula with date and criteria

Hi,

I have tried almost all the posts on this, and none of them are working....I am about to manual count all this data and just type in the results

I have a spreadsheet, and I want to choose a range of dates AND 2 criteria from another column.

So, if date is between 4/12/04 and 4/30/04 AND column x contains IP or SO, then count. I need to do this for every week up until the end of the year.

Anyone have any advise. My company is Still on Excel 97.

Thanks so much for your help.

Kim

2. ## Re: Excel formula with date and criteria

=SUMPRODUCT((A1:A30>=--"12/04/2004")*(A1:A30<=--"30/04/2004")*((X1:X30="IP")+(X1:X30="SO")))

where A1:A30 contains the dates and X1:X30 contains the text. Note that I have used UK dates to match my settings. Change to US dates if necessary.

3. ## Re: Excel formula with date and criteria

Hi... thanks. I get an #N/A as a result.

Here is my formula:
=SUMPRODUCT((forecast>=--"12-Apr-04")*(forecast<=--"29-Apr-04")*((pipe="IP")+(pipe="SO")))

Even if I switch the way the date is displayed (4/12/04) I still get an #n/a.

BUT if I take out the criteria for IP or SO, it counts the correct number of records between the dates.... So it is the IP and SO that are causing the problem.

Any other thoughts? Thanks again!

4. ## Re: Excel formula with date and criteria

Do the ranges forecast and pipe have the same number of rows? They must have or SUMPRODUCT will return #N/A.

5. ## Re: Excel formula with date and criteria

UGGG Thank you soooo much. I never would have figured that out!

Works like a charm now!

Thanks again!

#### Posting Permissions

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