If you're trying to SUM() a column based on criteria, then SUMIF() or SUMIFS() would get you what you want. In this example you could use SUMIFS()
=SUMIFS(P:P, A:A, "PG", F, "Indexed") - this gives you a SUM of values in P where Column A = "PG" and Column F = "Indexed". You could extend these criteria for as many filters as you need.
You'd then change this ...
Since you use a single cell to input both your data and formula, you should change your "formula" from =HOUR("17:30:00") - Hour("9:00:00") to simply
=("17:30:00" - "9:00:00")*24
The above formula will give you the desired result 8.5.
IMPORTANT. Make sure you format your cells as Number
( by going to: Format > Number > Number)
You could use a query formula instead:
=QUERY(A1:D14 ;"SELECT D WHERE D like 'MY%' and not A like 'MY%'";1)
Please read more about QUERY
Note that if you use a different Locale in your sheet you would have to change ; to , for the formula to work.
In my test, changing the row reference to a column reference (column U in this case), the above formula returns the NEXT-to-last non-zero value. I was able to get the LAST value as follows, but I don't know whether this formula would work generally or there is something particular about my spreadsheet that requires the "+1," so I hesitate to recommend this ...