SUMIFS less than date AND amount is more than zero

[-VAN-]

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?

 

[MARZIOTULLIO]

Can you provide some sample data and what the expected outcome is?


maybe

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

 

[-VAN-]

<tbody>
[TD="class: xl65, colspan: 3"] ABC COMPANY INVOICES & PAYMENT
[/TD]
[TD="class: xl69, width: 72"][/TD]
[TD="class: xl69, width: 72"][/TD]
[TD="class: xl80, width: 72"] FY06 [/TD]
[TD="class: xl81, width: 72"] FY07 [/TD]

[TD="class: xl67"] DATE (A) [/TD]
[TD="class: xl76"] INVOICE(B)
[/TD]
[TD="class: xl68, align: right"] Amount (C)
[/TD]
[TD="class: xl69"]
[/TD]
[TD="class: xl84"] INVOICED [/TD]
[TD="class: xl82"] (F2)
[/TD]
[TD="class: xl79"][/TD]

[TD="class: xl70"]30/07/2005[/TD]
[TD="class: xl77, align: center"]00063333[/TD]
[TD="class: xl72, align: right"]$710.05[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl85"] PAID [/TD]
[TD="class: xl83"] (F3)[/TD]
[TD="class: xl78"][/TD]

[TD="class: xl70"]1/08/2005[/TD]
[TD="class: xl77, align: center"]00063360[/TD]
[TD="class: xl73, align: right"]$934.55[/TD]
[TD="class: xl71"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]20/08/2005[/TD]
[TD="class: xl75, align: center"]PAYMENT[/TD]
[TD="class: xl74, align: right"] -$1,644.60 [/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]20/08/2005[/TD]
[TD="class: xl75, align: center"]00063636[/TD]
[TD="class: xl72, align: right"]$350.90[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]3/09/2005[/TD]
[TD="class: xl75, align: center"]00063899[/TD]
[TD="class: xl73, align: right"]$242.00[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]5/09/2005[/TD]
[TD="class: xl75, align: center"]00064631[/TD]
[TD="class: xl73, align: right"]$29.90[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]24/09/2005[/TD]
[TD="class: xl75, align: center"]00064662[/TD]
[TD="class: xl73, align: right"]$500.70[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]18/10/2005[/TD]
[TD="class: xl75, align: center"]00065784[/TD]
[TD="class: xl73, align: right"]$74.80[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]25/11/2005[/TD]
[TD="class: xl75, align: center"]PAYMENT[/TD]
[TD="class: xl74, align: right"] -$1,198.30 [/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]12/11/2005[/TD]
[TD="class: xl75"]00065832[/TD]
[TD="class: xl72, align: right"]$96.80[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]17/11/2005[/TD]
[TD="class: xl75, align: center"]00065999[/TD]
[TD="class: xl73, align: right"]$500.45[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]17/11/2005[/TD]
[TD="class: xl75, align: center"]00066014[/TD]
[TD="class: xl73, align: right"]$110.00[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]9/12/2005[/TD]
[TD="class: xl75, align: center"]00067826[/TD]
[TD="class: xl73, align: right"]$396.00[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]14/12/2005[/TD]
[TD="class: xl75, align: center"]00068560[/TD]
[TD="class: xl73, align: right"]$462.00[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]17/12/2005[/TD]
[TD="class: xl75, align: center"]00068587[/TD]
[TD="class: xl73, align: right"]$85.80[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl70"]4/01/2006[/TD]
[TD="class: xl75, align: center"]PAYMENT[/TD]
[TD="class: xl74, align: right"] -$1,651.05 [/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

</tbody>

I hope this extract is ok.

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

 

[-VAN-]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="colspan: 3"]ABC COMPANY INVOICES & PAYMENT HISTORY[/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"]FY06[/TD]
[TD="width: 72"]FY07[/TD]
[/TR]
[TR]
[TD]

DATE
(A)
​

[/TD]
[TD="align: center"]INVOICE (B)[/TD]
[TD="align: center"]Amount (C)[/TD]
[TD][/TD]
[TD]INVOICED[/TD]
[TD] (F2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/07/2005[/TD]
[TD="align: center"]00063333[/TD]
[TD="align: right"]$710.05[/TD]
[TD][/TD]
[TD]PAID[/TD]
[TD] (F3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/08/2005[/TD]
[TD="align: center"]00063360[/TD]
[TD="align: right"]$934.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/08/2005[/TD]
[TD="align: center"]PAYMENT[/TD]
[TD="align: right"]-$1,644.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/08/2005[/TD]
[TD="align: center"]00063636[/TD]
[TD="align: right"]$350.90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/09/2005[/TD]
[TD="align: center"]00063899[/TD]
[TD="align: right"]$242.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/09/2005[/TD]
[TD="align: center"]00064631[/TD]
[TD="align: right"]$29.90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24/09/2005[/TD]
[TD="align: center"]00064662[/TD]
[TD="align: right"]$500.70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/10/2005[/TD]
[TD="align: center"]00065784[/TD]
[TD="align: right"]$74.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25/11/2005[/TD]
[TD="align: center"]PAYMENT[/TD]
[TD="align: right"]-$1,198.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/11/2005[/TD]
[TD="align: center"]00065832[/TD]
[TD="align: right"]$96.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/11/2005[/TD]
[TD="align: center"]00065999[/TD]
[TD="align: right"]$500.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/11/2005[/TD]
[TD="align: center"]00066014[/TD]
[TD="align: right"]$110.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/12/2005[/TD]
[TD="align: center"]00067826[/TD]
[TD="align: right"]$396.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/12/2005[/TD]
[TD="align: center"]00068560[/TD]
[TD="align: right"]$462.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/12/2005[/TD]
[TD="align: center"]00068587[/TD]
[TD="align: right"]$85.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/01/2006[/TD]
[TD="align: center"]PAYMENT[/TD]
[TD="align: right"]-$1,651.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks for replying Marzio.

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

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!