Results 1 to 8 of 8

Thread: Formula Help for Counting with conditions

  1. #1

    Formula Help for Counting with conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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.


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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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. #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. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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)))


  7. #7
    Thanks NBVC, Everything is working now!

    Cheers!

  8. #8
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

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