Forum Discussion

jlharlan's avatar
jlharlan
Copper Contributor
Jan 23, 2023

Excel Formula

Hello,   I'm hoping someone can help me with a formula.  I have a large spreadsheet that I need to get a sum of based on certain criteria. Example:  If Date = 10/8/2022 and Cost Center = 10331, th...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 23, 2023

jlharlan 

=SUMIFS(PPE!$C$2:$C$29,PPE!$A$2:$A$29,A2,PPE!$B$2:$B$29,B2)

If the sheet with the complete data of PPE, Cost Ctr and Total Hours is named "PPE" you can try this formula.

=SUMPRODUCT((A2=PPE!$A$2:$A$29)*(B2=PPE!$B$2:$B$29)*PPE!$C$2:$C$29)

An alternative could be SUMPRODUCT.

 

Sheet "PPE":

  • jlharlan's avatar
    jlharlan
    Copper Contributor
    Jan 23, 2023

    OliverScheurich 

     

    Thanks, but that didn't work.

     

    I'm creating the formulas on one worksheet (ie: Totals) from another worksheet (ie: all PPE).

    The formula needs to add all of the Cost Center for 10331 from all of the PPE 10/8/2022.

    I didn't see any reference in the formula you provided to equal the cost center.

    Thanks for helping.

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jan 24, 2023

      jlharlan 

      I don't understand why it doesn't work in your sheet. The sum of e.g. Cost Center for 10331 from all of the PPE 10/8/2022 is 468 and this is returned by the formulas.

       

      =SUMIFS(PPE!$C$2:$C$29,PPE!$A$2:$A$29,A2,PPE!$B$2:$B$29,B2)

       

      "B2" in the SUMIFS formula refers to cost center 10331.

      Maybe you can share your expected results for the sum of the cost centers of the PPE sheet.

       

      Sheet "PPE":

Resources