Sumifs Criteria, less than, greater than

**telton** · 08-26-2007, 10:44 PM

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 !!!

**NBVC** · 08-27-2007, 08:19 AM

TRY:

=SUMIFS(E4:E9999,G4:G9999,"<"&$H$1,H4:H9999,"Unpaid")

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 !!!

Try:

=SUMIFS(E4:E9999,G4:G9999,"<"&$H$1,H4:H9999,"")

EDIT: misinterpreted 2nd question...hopefully this works....I don't have 2007...so just guessing.

**sicelo ndlovu** · 05-07-2013, 10:19 AM

Originally Posted by telton

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 !!!

Try =today() instead of now!!, now returns the current time too which is not the format intended for h1 cell.