Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2003 (2003)

    I am trying to do a sum with two IF statements. Why will this not work?

    =SUM(IF(Assumptions!C34:C129="Expense",IF(Assumpti ons!D34129="Hardware",Assumptions!F34:F129),0))

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

    Re: Excel 2003 (2003)

    Try the following formula:

    =SUMPRODUCT((Assumptions!C34:C129="Expense")*(Assu mptions!D34129="Hardware")*Assumptions!F34:F129)

    or the following array formula (confirm with Ctrl+Shift+Enter:

    =SUM(IF((Assumptions!C34:C129="Expense")*(Assumpti ons!D34129="Hardware"),Assumptions!F34:F129))

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Excel 2003 (2003)

    Welcome to the Lounge.

    It is difficult to understand what you are trying to do with the attached formula.

    Perhaps you should try the SUMIF Function, that may be what you want.

    You could try a formula such as SumIf(Assumptions!C34:C129,"Expense",Assumptions!F 34:F129)+SumIf(Assumptions!D34129,"Hardware",Assumptions!F34:F129)
    This formula will evaluated range C:34 to C129 for Expense and add up the values in range F34:F129 then it will evaluate range D34129 for "Hardware" and add up the values in range F34:F129. Then it will add up both results.

    If you are trying to contruct a formula that will will evalutate Cols C and Cols D and only return results when both Expense and Hardware occur you would need to use the AND function.

    Have you considered a Pivot Table? This could show the results for Expense and Results for Hardware is a Table.

    Tom Duthie

Posting Permissions

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