# Thread: Formula Help for Counting with conditions

1. ## Formula Help for Counting with conditions

The formula I need assistance with is with the following:

If Column C is equal to text string, and column A is a date within the month of April (1st to 31st) 2011 and column B is any date before the month (not including) April (including prior years), then count the number of instances Column B’s data is a date before the month of april.

Currently the data is in a table so for example Column A is “Invoice Date”, Column B is “Shipping Date”.
If I could get a formula for this that I can modify for every month, EX: replace Column A requirement for May, June, July.. etc and for each month and Shipping Date that would be any date prior to Column A’s Month.

Thanks for the help!

2. Try something like:

=SUMPRODUCT(--(\$C\$2:\$C\$5<>""),--(\$A\$2:\$A\$5>=DATE(2011,4,1)),--(\$A\$2:\$A\$5<=DATE(2011,4,31)),--(\$B\$2:\$B\$5<DATE(2011,4,1)))

3. I tried plugging in that formula with the ranges for the table inputted.

Did I miss something as it is kicking out "0" but for this data set the answer should be a count of 1

4. This is only looking at the rows where Business Unit column doesn't equal exactly "MABU". Is that the case?

Also, you are sure the dates are entered as dates and not text strings that look like dates?

Is it possible to post a sample workbook? (no confidential data)

5. If i take out (--(MABU[Business Unit]<>"MABU") it works.

I think the issue is that I need the coding to be exactly equal MABU.

6. Then it would be:

7. Thanks NBVC, Everything is working now!

Cheers!

8. Hi

Just a quick "heads up" on date - it should be DATE(2011,4,30) - not 31

If you are going to use Sumproduct, then you would be better advised to hold the 3 relevant dates in cells, then the formula remains constant, and you just change your parameters

With start date in say cell M1, End date in N1 and Shipping date in cell O1 then

It might be much easier to use a Pivot Table to give you the results you want.

#### Posting Permissions

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