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

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".
Cell F3 should read "-$4493.95"

 

[-VAN-]

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

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!