# SUMIFS less than date AND amount is more than zero

#### -VAN-

##### New Member
I am trying to do a sum based on two separate criteria:

=SUMIFS(A3:A163,"<=30/06/2006",C3:C136,">=0",c3:c136)

Col A = dates
Col C = Amounts (there are negative and positive amounts)

So basically, I am grouping financial years (Aus) that are not negative numbers (invoiced amounts) and a separate one for negative numbers (payments).

What am I doing wrong?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### MARZIOTULLIO

##### Well-known Member
Can you provide some sample data and what the expected outcome is?

maybe

=SUMIFS(C3:C136,C3:C136,">0",A3:A163,"<=30/06/2006")

Last edited:

#### -VAN-

##### New Member
 ABC COMPANY INVOICES & PAYMENT FY06 FY07 DATE (A) INVOICE(B) Amount (C) INVOICED (F2) 30/07/2005 00063333 \$710.05 PAID (F3) 1/08/2005 00063360 \$934.55 20/08/2005 PAYMENT -\$1,644.60 20/08/2005 00063636 \$350.90 3/09/2005 00063899 \$242.00 5/09/2005 00064631 \$29.90 24/09/2005 00064662 \$500.70 18/10/2005 00065784 \$74.80 25/11/2005 PAYMENT -\$1,198.30 12/11/2005 00065832 \$96.80 17/11/2005 00065999 \$500.45 17/11/2005 00066014 \$110.00 9/12/2005 00067826 \$396.00 14/12/2005 00068560 \$462.00 17/12/2005 00068587 \$85.80 4/01/2006 PAYMENT -\$1,651.05

<tbody>
</tbody>

I hope this extract is ok.

Cell F2 is where I am applying this formula. The results should read "\$4493.95".

#### -VAN-

##### New Member
 ABC COMPANY INVOICES & PAYMENT HISTORY FY06 FY07 DATE (A)​ INVOICE (B) Amount (C) INVOICED (F2) 30/07/2005 00063333 \$710.05 PAID (F3) 1/08/2005 00063360 \$934.55 20/08/2005 PAYMENT -\$1,644.60 20/08/2005 00063636 \$350.90 3/09/2005 00063899 \$242.00 5/09/2005 00064631 \$29.90 24/09/2005 00064662 \$500.70 18/10/2005 00065784 \$74.80 25/11/2005 PAYMENT -\$1,198.30 12/11/2005 00065832 \$96.80 17/11/2005 00065999 \$500.45 17/11/2005 00066014 \$110.00 9/12/2005 00067826 \$396.00 14/12/2005 00068560 \$462.00 17/12/2005 00068587 \$85.80 4/01/2006 PAYMENT -\$1,651.05

<tbody>
</tbody>

I hope the above is ok.

Cell F2 is where I am putting the formula. It should pick up from the dates (col A) all the dates <="30/06/06" and any amount that is higher than "0". The result should read "\$4493.95".
Cell F3 would be the same criteria, only if negative number (ie: PAYMENT), should read "-\$4493.95".

The other formula brought up a VALUE error message.

#### -VAN-

##### New Member

I have tried posting twice already a table with info, but it doesn't seems to be approved?

The alternative formula yielded a value error.

Basically, Col A is a list on running dates.
Col B is invoice numbers or "Payment"
Col C is the invoice amount (more than zero amount) or payment amount (less than zero, negative number).

Cell F3, I have for each FY the corresponding amounts for that financial year, and F4 is the payments made for that FY.

I hope that makes sense?

I have also tried typing the end date in F2 (eg: 30/06/06), in case it didn't like the format, but it yielded a "0" result.

=SUMIFS(C3:C25,A3:A25,"<=F2",C3:C25,">=\$0")

I am now confused as to what alternatives I can use?

#### -VAN-

##### New Member
I have worked it out!

=SUMIFS(C3:C25,A3:A25,"<="&F2,C3:C25,">=\$0") works!

I forgot to add the trusty "&" sign to the cell reference F2.

It is producing desired results YAY!

Replies
2
Views
690
Replies
4
Views
478
Replies
1
Views
169
Replies
1
Views
261
Replies
3
Views
319

1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back