Formula Help for Counting with conditions

Thorcorp

New member
Joined
May 26, 2011
Messages
6
Reaction score
0
Points
0
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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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)))

adjusting ranges to suit.
 

Thorcorp

New member
Joined
May 26, 2011
Messages
6
Reaction score
0
Points
0
I tried plugging in that formula with the ranges for the table inputted.

=SUMPRODUCT(--(MABU[Business Unit]<>"MABU"),--(MABU[Invoice Date]>=DATE(2011,4,1)),--(MABU[Invoice Date]<=DATE(2011,4,31)),--(MABU[Shipping Date]<DATE(2011,4,1)))

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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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
Joined
May 26, 2011
Messages
6
Reaction score
0
Points
0
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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Then it would be:

=SUMPRODUCT(--(MABU[Business Unit]="MABU"),--(MABU[Invoice Date]>=DATE(2011,4,1)),--(MABU[Invoice Date]<=DATE(2011,4,31)),--(MABU[Shipping Date]<DATE(2011,4,1)))
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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
=SUMPRODUCT(--(MABU[Business Unit]="MABU"),--(MABU[Invoice Date]>=$M$1),--(MABU[Invoice Date]<=$N$1),--(MABU[Shipping Date]<$O$1))

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