Only calculate if a cell has an X

[mrkris1982]

I have a chart that is populated, collecting data from each month of 2017 and I'd like to reuse this for 2018, but need to modify to cut down on the work.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]App[/TD]
[TD]Month[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="align: right"]Expected Uptime[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Downtime[/TD]
[TD]Total Uptime[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]App1[/TD]
[TD]Jan[/TD]
[TD]737:15:00[/TD]
[TD]10:00:00[/TD]
[TD]727:15:00[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]App2[/TD]
[TD]Jan[/TD]
[TD]744:00:00[/TD]
[TD]4:44:00[/TD]
[TD]740:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]App1[/TD]
[TD]Feb[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: right"]465:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5:00:00[/TD]
[TD]460:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]App2[/TD]
[TD]Feb[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: right"]558:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8:00:00[/TD]
[TD]550:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]App3[/TD]
[TD]Feb[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: right"]651:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1:00:00[/TD]
[TD]650:00:00[/TD]
[/TR]
</tbody>[/TABLE]

In another section, I am doing calculations for monthly totals per app, pulling from the chart above

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]App 1 Total Uptime[/TD]
[TD]App 2 Total Uptime[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]727:15:00[/TD]
[TD]740:00:00[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]460:00:00[/TD]
[TD]550:00:00[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]465:00:00[/TD]
[TD]548:00:00[/TD]
[/TR]
[TR]
[TD]Actual uptime total[/TD]
[TD]=SUM(D2:D13)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expected Uptime Total[/TD]
[TD]xxxxxxxxxxxx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Percentage Up[/TD]
[TD]=B5/B6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

for the xxxxxxxxxx I need help

In my real spreadsheet, its doing a sum

=SUM(Q3+Q21+Q39+Q57+Q75+Q93+Q111+Q129+Q147+Q165+Q183+Q201)

where Q would equal the Chart 1 > Expected Uptime column above

Id like to leave the equation exactly as it is BUT only include it in the calculation of xxxxxxxxxx if there is an x in the adjacent column A cell

This would allow me to keep the chart in tact with the calculation only caring about the expected uptime for that app if there is an x in that apps column A cell for that month.

Hard to explain, but I am willing to give anything a try if someone wants to take a crack at it.

 

[Special-K99]

You don't SUM.
This can be replaced with this

=Q3+Q21+Q39+Q57+Q75+Q93+Q111+Q129+Q147+Q165+Q183+Q201

 

[Eric W]

Try:

=SUM(IF(MOD(ROW(Q3:Q201),18)=3,IF(A3:A201="x",Q3:Q201)))

confirmed with Control+Shift+Enter

 

[mrkris1982]

Eric W: I tried your suggestion and the cell results in a value of 0:00:00 even after placing an x in a couple cells (A3 and A4) for the first 2 apps. The result should be 496:00:00 for App 1 and 737:15:00 for App 2 in the real workbook.