+ Reply to Thread
Results 1 to 3 of 3

Problems with argument formula

  1. #1
    Registered User
    Join Date
    08-03-2010
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    5

    Problems with argument formula

    Please see attached spreadsheet.

    I am having trouble figuring out why the formula on the 'UCA Summary Data' tab for the "Definitized in FY10" chart does not work for the month of January. I am simply trying to count the number of items that were definitized this month, but it works for every othe month but January.

    I also have another problem with the 'UCA Summary Data' tab. The formulas I have in the "Overage in FY10" and "Overage in FY11" charts seems to miss counting items that become overage at the beginning of each month or at the end of the fiscal year. Can anyone help me with either/both issues?

    Attached Files Attached Files
    Last edited by DCMADiva87; 08-04-2010 at 11:47 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problems with argument formula

    Blanks are seen as 0 and dates are numbers such that 0 on a 1900 Date System is 0 Jan 1900.
    It follows that given you're testing only for the month (and not the year) these Jan 1900 values are included in your result.

    To resolve either

    a) add a further test to the SUMPRODUCT to account for blanks

    =SUMPRODUCT(--ISNUMBER('Definitized UCAs'!$L$3:$L$6000),--(TEXT('Definitized UCAs'!$L$3:$L$6000,"mmm")=E16))

    b) add year to the string

    =SUMPRODUCT(--(TEXT('Definitized UCAs'!$L$3:$L$6000,"mmmyyyy")=E16&YEAR($A17)+1))

  3. #3
    Registered User
    Join Date
    08-03-2010
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Problems with argument formula

    Thank you so much! Problem solved!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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