Forum Discussion

Jennifer Juneau's avatar
Jennifer Juneau
Copper Contributor
Apr 24, 2017

sumifs using multiple criteria

I have a spreadsheet called Stats with named columns STATS (which holds status of either Open, On Hold, Closed - Loss....etc), MARKET (which holds location of either PHX, NYC, FL, etc.) and column called POSITIONSNEED (which holds a #).  I want to COUNT the # of positoins needed based on the criteria Market and Status.  I tried using an array but it is just counting the POSTIONSNEED column irregardless of STATUS or MARKET.  

 

 

 

 

Any help would be appreciated.  :-)

 

 

 

 

 

Formulas & Functions
Need Help

5 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 24, 2017

    Hi Jennifer,

     

    For AND criteria you may use SUMIFS

    =SUMIFS(PositionsNeed,Market,K16,Status,B8)
    • Jennifer Juneau's avatar
      Jennifer Juneau
      Copper Contributor
      Apr 24, 2017

      Thanks Sergei, but that didn't work.  Where is that formula do you define the criteria .  Just having a comma between everything doesn't say Market must = k16's value...  , right?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Apr 24, 2017

        Jennifer, i generated small test file. Not sure i understood your first picture, based on second one:

         

         

        Formula in H7 calculates number of positions for the market in J6 with status as in G10. Calculation is made on columns C:C (Market), D:D (PositionNeeded) and F:F (Status).

         

        That's only to illustrate the formula.

         

        File is attached

Resources