Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Issue ( Complex sum )

Options
  • 08-03-2013 10:36pm
    #1
    thecroghanlad
    Registered Users Posts: 3 ✭


    Hi

    Appreciate some help out there if anyone can oblige

    I have the below worksheet with data that I need to manipulate to go into another sheet


    AREA 1 SYSTEM 1 CORE EVENING WEEKEND
    N Y 7 0 0
    Y N 15 0 0
    Y Y 51 0 0
    Y Y 45 0 0


    I want to get a sum of the core figures if AREA 1 and SYSTEM 1 are both "Y"
    I have got a count of the event using the following formula

    =SUMPRODUCT(--(data!A2:A5="Y"),--(data!B2:B5="Y"))

    Core Evening Weekend
    Area 1 2
    SYSTEM 1 2


    So what I would be expecting is that core would have a total of 96 and so on and on

    Any thoughts ???


Comments

  • Options
    #2
    Mort5000
    Registered Users Posts: 419 ✭✭Mort5000


    Option 1:
    Use 1 and 0 as your Y and No indicators and then just multiply all the columns, area x system x core

    Option 2:
    Use nested IF functions to conditionally get your values.
    =IF(B15="y",IF(C15="y",D15,0),0)


  • Options
    #3
    SalteeDog
    Registered Users Posts: 450 ✭✭SalteeDog


    You can use the SUMIFS function - which is specifically for this purpose.

    i.e =SUMIFS(C2:C5,A2:A5,"Y",B2:B5,"Y") will do what you need.


    SUMIFS, SUMIF and indeed COUNTIF are very handy functions.


  • Options
    #4
    thecroghanlad
    Registered Users Posts: 3 ✭thecroghanlad


    Thanks folks - I was over complicating things - both solutions are exactly what I needed - Regards


Advertisement