Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF Help (Excel 2000)

    Hello!!!

    I have an SUMIF statement that I would like to add additional criteria into.

    =(SUMIF(EDate,"<="&SDate,J_Ph_Calls)-SUMIF(EDate,"<"&FOW,J_Ph_Calls))

    I have one more piece of criteria that I would like to fit in here, but am (as always) having a hard time. Is it possible, and if so where would I add
    IF(EAssoc=$A4)
    into this formula?

    Mucho Thanks!

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

    Re: SUMIF Help (Excel 2000)

    It's time for a different type of formula:

    =SUMPRODUCT((EDate<=SDate)*(Edate>=FOW)*(EAssoc=$A 4)*J_Ph_Calls)

    You can add as many conditions as you like

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Help (Excel 2000)

    Fabulous, oh man that never rests!

    I did have to re-define my named ranges from $A:$A to $A$##:$A$#### to get the formula to work. Why is that?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF Help (Excel 2000)

    According to MS in Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003 it is implied that it is "by design"

    Though why A2:A65536 or A1:A65535 would be allowed and not A1:A65536 is beyond me... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I guess 1 cell can make a lot of difference...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Help (Excel 2000)

    Entire rows are OK though. Here some thoughts regarding the issue:

    http://tinyurl.com/ahysg
    Microsoft MVP - Excel

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

    Re: SUMIF Help (Excel 2000)

    I don't know the exact "why", but array and array-type formulas only work with finite ranges, not with entire rows or columns.

    Added later: see Aladin Akyurek's reply below - entire rows are OK. (Thanks, Aladin!)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF Help (Excel 2000)

    Thanks for the clarification and the information...

    Steve

Posting Permissions

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