SUMIFS less than date AND amount is more than zero

-VAN-

New Member
Joined
Sep 14, 2013
Messages
7
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?
 

Some videos you may like

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
Joined
Aug 22, 2015
Messages
767
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
Joined
Sep 14, 2013
Messages
7
ABC COMPANY INVOICES & PAYMENT
FY06FY07
DATE (A)INVOICE(B)
Amount (C)

INVOICED (F2)
30/07/200500063333$710.05PAID (F3)
1/08/200500063360$934.55
20/08/2005PAYMENT-$1,644.60
20/08/200500063636$350.90
3/09/200500063899$242.00
5/09/200500064631$29.90
24/09/200500064662$500.70
18/10/200500065784$74.80
25/11/2005PAYMENT-$1,198.30
12/11/200500065832$96.80
17/11/200500065999$500.45
17/11/200500066014$110.00
9/12/200500067826$396.00
14/12/200500068560$462.00
17/12/200500068587$85.80
4/01/2006PAYMENT-$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-

New Member
Joined
Sep 14, 2013
Messages
7
ABC COMPANY INVOICES & PAYMENT HISTORYFY06FY07
DATE
(A)
INVOICE (B)Amount (C)INVOICED (F2)
30/07/200500063333$710.05PAID (F3)
1/08/200500063360$934.55
20/08/2005PAYMENT-$1,644.60
20/08/200500063636$350.90
3/09/200500063899$242.00
5/09/200500064631$29.90
24/09/200500064662$500.70
18/10/200500065784$74.80
25/11/2005PAYMENT-$1,198.30
12/11/200500065832$96.80
17/11/200500065999$500.45
17/11/200500066014$110.00
9/12/200500067826$396.00
14/12/200500068560$462.00
17/12/200500068587$85.80
4/01/2006PAYMENT-$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
Joined
Sep 14, 2013
Messages
7
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-

New Member
Joined
Sep 14, 2013
Messages
7
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top