Help with Formula

[sajohnson05]

I have two tabs with a list of different sites. I am needing to get cost amounts based on the type of work and also a count of line items per system. Below i have shown what is on one tab and the other tab where i need sum and count. Any help would be great. Thanks

Tab 1

Division	Site	System	Eqpt	Description	Item Code	Cost
Star	Ville MC	Air Distribution	AHU01 -OIC	Smoke Detector is on unit but not being read by Metasys	AS	$485.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Dampers do not have the same hysteresis	CM	$420.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Bad OA humidity sensor	CM	$331.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Exhaust damper actuator bad & damper stuck closed	CM	$1,012.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Add low limits for proper coverage	CM	$838.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Clean Air Flow rings and repair flex	FM	$280.00
Star	Ville MC	Air Distribution	AHU01 -OIC	Clean OSA intake grills	FM	$834.00
Star	Ville MC	Air Distribution	AHU01 -OIC	damper/valves with actuators that have feed back	AE	$4,388.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

# of line items per system

Tab 2

Division

Site

Total CM

Total FM

Total Maintenance

Cost Total AS

Total AE

Total UL

Total AU

Grand Total

Air Distribution

CHW

HW

<colgroup><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Tyler Hilton]

Hi sajohnson05,

Assuming your table is columns A thru G, use the following formulas:

Total Cost Amount by Type of Work: =SUMIFS(G:G,F:F,Reference) where Reference is the Type of Work you are summing (in this example I'm assuming Item Code - column F - represents the Type of Work)

Count of Line Items per System: =COUNTIFS(C:C,Reference) where Reference is the System you are counting (i.e. "Air Distribution" or a cell reference containing "Air Distribution")

Best,

Tyler Hilton

 

[sajohnson05]

Thank you. The only thing that I failed to mention is that I will have 53 different sites and would need a be able to sum and count by that site

 

[Tyler Hilton]

Thank you. The only thing that I failed to mention is that I will have 53 different sites and would need a be able to sum and count by that site

Hi sajohnson05,

To sum and count by site, simply add that range and site reference to the end of each formula:

=SUMIFS(G:G,F:F,TypeReference,B:B,SiteReference)

=COUNTIFS(C:C,SystemReference,B:B,SiteReference)

Hope this helps.

Best,

Tyler Hilton

 

[sajohnson05]

Thank you. That was exactly what i needed.

 

[Tyler Hilton]

Perfect. Thanks for the feedback.

Cheers!

Tyler Hilton