+ Reply to Thread
Results 1 to 6 of 6

Correct Formula not giving results

  1. #1
    Registered User
    Join Date
    09-19-2012
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Correct Formula not giving results

    Hi

    Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).

    Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates

    I created the following condition for each product code under each month in SHEET1

    =IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")

    The formula only works for two product codes (A and B), and then yields blank cells.

    Can anyone suggest why?
    Thanks
    NJ007

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Correct Formula not giving results

    Hi,

    Could you please upload your workbook so that I can debug the formula?

    Thanks

  3. #3
    Registered User
    Join Date
    09-19-2012
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Correct Formula not giving results

    Thanks

    Sheet is attached. There is something fundamentally wrong with my formula I reckon. Not sure how if could be achieved with sumproduct.

    Cheers
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Correct Formula not giving results

    Hi,

    Is this what you were looking for:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ???

    There is going to be a problem with column A on Sheet1, as formulae don't like dealing with merged cells. So I have created a new (hidden) column A, which has the month on each row, to make the formula draggable down column D.

    Workbook attached. I hope this helps

  5. #5
    Registered User
    Join Date
    09-19-2012
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Correct Formula not giving results

    You are a wizard!!!
    Really appreciate it

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Correct Formula not giving results

    I'm a what!?!?! (see the Harry Potter reference there )

    You're welcome.

    Please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-31-2012, 02:09 PM
  2. Formula giving unexpected results
    By johnmerlino in forum Excel General
    Replies: 6
    Last Post: 12-01-2010, 01:20 AM
  3. IF Formula not giving expected results.
    By fungus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-17-2010, 02:10 PM
  4. Formula results giving 29/01/1990?
    By Finalfrontier1976 in forum Excel General
    Replies: 2
    Last Post: 03-24-2010, 03:46 PM
  5. Sumproduct formula not giving expected results
    By Shocked in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-30-2008, 03:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1