Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013

    Unanswered: Sumifs with multiple criteria from same colum

    I have the following sumifs that is an export from a access query. I am required to calculate the total of incidents between a date range and grouping sites so that the client can see sites that are internal and off sites

    QryFireAlarmsImported!B:B = The number of incidents
    QryFireAlarmsImported!D = The date of the Incident
    QryFireAlarmsImported!E10 = 01/11/13
    QryFireAlarmsImported!E11 = 30/11/13


    QryFireAlarmsImported!A:A,{"Regency Hall","West Quad"})

    Is where the problem is I need to be able to add multiple sites that it will look for, currently it is just calculating the first site mentioned and no further sites

    Here is the sumifs as it stands

    =SUMIFS(QryFireAlarmsImported!B:B,QryFireAlarmsImp orted!D,">="&QryFireAlarmsImported!E10,QryFireAlarmsImpor ted!D,"<="&QryFireAlarmsImported!E11,QryFireAlarmsImpor ted!C:C,"FCH",QryFireAlarmsImported!A:A,{"Regency Hall","West Quad"})

    Thank you for your help


  2. #2
    Join Date
    Sep 2008
    London, UK
    In your formula, SUMIFS() will return an array of 2 values, one for each value in the array constant. You need to sum them together which you can do by surrounding your formula with SUM(). If you need more info on this then let me know and I'll give you a link.
        ,QryFireAlarmsImporte  d!A:A,{"Regency Hall","West Quad"}))

  3. #3
    Join Date
    Feb 2013


    Thank you Colin

    Surround the sumifs with a sum and resolved the problem, much appriciated

    Kind Regards


  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    If the data is in access why mess around with excel, why not use the power of sql to do this sort of thing in queries
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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