Results 1 to 3 of 3
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Sumif with formula as criteria (Excel 2003/SP2)

    The goal here is a simplified pie chart. If categories are less than 10% of the total value they should be amalgamated into the category "Other".
    When calculating the Other category I'm currently using the following formula:
    =SUMIF(F2:F15,2,B2:B15) Where the value 2 is the type returned by the cells. Ideally, I'd like to include the type function in the criteria for the formula - like this =SUMIF(e2:e15,type(e2:e15)=2,B2:B15). However that formula doesn't work and neither does
    {=SUM(IF(TYPE(E2:E15)=2,B2:B15))}

    Obviously, my solution which requires type to be evaluated externally is working I would just find it more satisfying to place the criteria into the formula.
    I've also run into the same problem when using the Month function as criteria.

    Any thoughts?
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Sumif with formula as criteria (Excel 2003/SP2)

    TYPE doesn't result in an array, but in a single value, hence you cannot use it. You can use the condition directly:
    <code>
    =SUMIF(B2:B15,"<"&GrandTotal/10,B2:B15)
    </code>
    or
    <code>
    =SUMPRODUCT((B2:B15<GrandTotal/10)*B2:B15)</code>

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Sumif with formula as criteria (Excel 2003/SP2

    OOh, thanks for clearing up my tunnel vision!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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