New Contributor

# 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.

 PPE Cost Ctr Total Hours 10/8/2022 10331 93.25 10/8/2022 10331 57.75 10/8/2022 10331 4.00 10/8/2022 10331 24.00 10/8/2022 10331 27.50 10/8/2022 10331 59.00 10/8/2022 10331 88.75 10/8/2022 10331 74.75 10/8/2022 10331 12.25 10/8/2022 10331 26.75 10/8/2022 10330 8.75 10/8/2022 10247 9.75 10/8/2022 10217 11.50 10/8/2022 10217 12.50 10/8/2022 10217 12.00 10/8/2022 10235 12.50 10/8/2022 10235 12.00 10/8/2022 10235 12.00 10/8/2022 10235 12.00 10/8/2022 10235 5.25 10/8/2022 10235 37.00 10/8/2022 10235 12.50 10/8/2022 10235 24.00 10/8/2022 10235 3.25 10/8/2022 10235 12.25 10/8/2022 10235 11.25 10/8/2022 10235 12.00 10/8/2022 10236 12.00

3 Replies

# Re: Excel Formula

=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":

# Re: Excel Formula

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.

# Re: Excel Formula

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":