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, then Sum Total Hours

There will be several rows with the same Date and Cost Ctr, I just need to add the Total Hours together and put them on a separate spreadsheet.

Thank you.

 

PPECost CtrTotal Hours
10/8/20221033193.25
10/8/20221033157.75
10/8/2022103314.00
10/8/20221033124.00
10/8/20221033127.50
10/8/20221033159.00
10/8/20221033188.75
10/8/20221033174.75
10/8/20221033112.25
10/8/20221033126.75
10/8/2022103308.75
10/8/2022102479.75
10/8/20221021711.50
10/8/20221021712.50
10/8/20221021712.00
10/8/20221023512.50
10/8/20221023512.00
10/8/20221023512.00
10/8/20221023512.00
10/8/2022102355.25
10/8/20221023537.00
10/8/20221023512.50
10/8/20221023524.00
10/8/2022102353.25
10/8/20221023512.25
10/8/20221023511.25
10/8/20221023512.00
10/8/20221023612.00

 

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