I have been trying to get function that sums my values based on 2 criteria.
One of the criteria involves a less that < criteria. I want this to be based on a cell where the value can change ie. The date.

The formula only works when I enter the actual figure not the referenced cell.

eg.

=SUMIFS(E4:E9999,G4:G9999,"<27/08/2007",H4:H9999,"Unpaid") works

but =SUMIFS(E4:E9999,G4:G9999,"<$H$1",H4:H9999,"Unpaid") doesn't

H1 has the formula =now()

Also. If anyone can advise me on how I can make the formula work with the reference to "Unpaid" being BLANK it would be appreciated !!!