SUMIFS with 2 values in Criteria 1

  • Hi all,


    I am trying to sum the items in Column C that corresponds only to Status (column A) "Firm Quote" and "Requote" and Sub Status (column B) "In Progress", the result should be 4000.


    Thank you!


    Status Sub Status items
    Budget On Hold 210
    Requote In Progress 2,000
    Firm Quote On Hold 585
    Firm Quote In Progress 500
    Requote In Progress 1,500
    Firm Quote On Hold 2,406
    Requote On Hold 2,141
    Budget In Progress 1,200
    Total   4,000
  • You can use:


    =SUM(SUMIFS(C:C,A:A,{"Firm Quote", "Requote" },B:B,"In Progress"))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Thank you so much, I was trying the other way around nesting the SUM(CountIf) in the Sumifs, no wonder it didn't work :D

  • Yup. :)


    Using the array in the criterion means that the SUMIFS returns an array of values, which you then need to sum up.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why