Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumproduct Problem (2003)

    I have a formula in the attached sheet that almost works. It looks at what date is entered and counts the occurances and places the total in the appropriate box on the table.

    My problem is that if the cells are blank it records the totals under January. If the cells are blank I need it to NOT record at all until a date is entered.

    How can this be corrected? Do I need a different formula? thanks.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct Problem (2003)

    =IF($F4="","",SUMPRODUCT(($C$4:$C$148=$F4)*(TEXT($ D$4:$D$148,"mmmm")=G$3)))

    Yes the formula seems to work except for the January issue. Where would I place the ISNA to correct this???

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct Problem (2003)

    Howdy. You can add an IF wrapper around it (simplified) (Sorry, after looking at sheet, you have that)

    I changed the SUMPRODUCT, and changed the headers in row 3. This is in G4

    =IF($F4="","",SUMPRODUCT(($C$4:$C$148=$F4)*($D$4:$ D$148>=G$3)*($D$4:$D$148<H$3)))

    Seems to work
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct Problem (2003)

    Note, I updated my post after you posted. If possible you can change the headers in row 3, to be the first day of month, then use those as references in your formula.

    Then change the formatting to Custom, mmmm, and they will appear as month names.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct Problem (2003)

    Try

    =IF($F4="","",SUMPRODUCT(($C$4:$C$148=$F4)*(TEXT($ D$4:$D$148,"mmmm")=G$3)*NOT(ISBLANK($D$4:$D$148))) )

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct Problem (2003)

    The NOT ISBLANK was what I needed thanks everyone for your help.

Posting Permissions

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