# Formula Help for Counting with conditions

#### Thorcorp

##### New member
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!

#### NBVC

##### Super Moderator
Staff member
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)))

#### Thorcorp

##### New member
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

#### NBVC

##### Super Moderator
Staff member
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)

#### Thorcorp

##### New member
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.

#### NBVC

##### Super Moderator
Staff member
Then it would be:

#### Thorcorp

##### New member
Thanks NBVC, Everything is working now!

Cheers!

#### Roger Govier

##### New member
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