SumIf with Named Ranges and Data Validation

[melissapedia]

I've tried to research the combination of variables I think could be causing the problem, but I'm stumped.

I have a time tracking spreadsheet (a modified time card) that I'm using to record hours worked on projects, both paid and unpaid. I have a calculation for each period of time worked, then a data validation to the left of the calculation that denotes U (unpaid) or P (paid).

I would like to place a subtotal in C27 for named range 'Sunday1' (C18,C21,C24) when the cell to the left, named range Sunday1UP (B18,B21,B24), has a value of U. Sunday1UP has data validations sourced on "Audit!H2:H3" (where U is Audit!H3).

My current formula for C27 is:
=SUMIF(Sunday1UP,"U",Sunday1) (I have also substituted "=Audit!H3" for "U" or "='U'")
=SUMIF(range,criteria,sum range)

My function arguments are turning up errors on named ranges.

These are the cells included in the named ranges. Sunday1 are all numeric results calculated from a formula such as '=(C17-C16)*24'. Sunday1UP are cells containing either 'U' or 'P', formatted as text.

Where am I going wrong?

Also, please disregard the tally in cell C26 - it is an old totaling calculation, not the Paid subtotal - yet.

 

[melissapedia]

**

 

[melissapedia]

I've done a little bit more testing. I think it has something to do with the data validation (U / P) in Sunday1UP (B18,B21,B24). Still stumped, though.

 

[melissapedia]

SOLVED

Looks like the formula throws a #VALUE! because of the non-contiguous ranges. Once I changed them to contiguous ranges, it worked just fine. I hadn't expected non-contiguous ranges to be an issue.

Sunday1UP became B16:B24 and Sunday1 became C16:C24; the fact that there are times listed in C16:17, C19:C20, C22:23 didn't end up mattering at all.