Sum under two conditions

Hi,

Would like to find out what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).

Appreciate your help.

Use Sumproduct....

e.g. =Sumproduct(--(A1:A100="unpaid"),--(Month(B1:B100)=3),C1:C100)

sums range C1:C100 where A1:A100 is unpain and B1:B100 is column containing dates.

With

Col_A containing invoice status (paid, unpaid) and A1: Status
Col_B containing due dates and B1: DueDate
Col_C containing amounts and C1: InvAmt

Try this:
E1: Unpaid
F1: 200803...representing YYYYMM

This formula returns the sum of Unpaid invoices due in Mar-2008:

Please Login or Register  to view this content.

Is that something you can work with?

Why the same formula (ie.. Sumproduct) has two method of doing it? Why must the formula suggested by NBVC and Ron Coderre must use "--" in front of the sumproduct... does it indicates anything for the Sumproduct formula?

Originally Posted by gilbert

Why the same formula (ie.. Sumproduct) has two method of doing it? Why must the formula suggested by NBVC and Ron Coderre must use "--" in front of the sumproduct... does it indicates anything for the Sumproduct formula?

See here for explanations and examples:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Originally Posted by NBVC

See here for explanations and examples:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Gee.. thanks.. this is a very useful website.

I have adopted the formulae suggested by daddylonglegs and it works well... Supposing now I have three criterias, ie. Unpaid and Half Paid and Due. I want to sum those Dues that are Unpaid and Half Paid.... how should I go about it? Also considering that we can't take the full invoice values for Half Paid account ... is there any other way for me to take the net amount without creating additional columns for amount paid and balance?

Enclose herewith the print screen for easy explanation

\1

Basically.. the invoice status, whether 'Paid','Unpaid','Half Paid' or 'FOC' is all in one coloumn. My intention is to show the unpaid and unpaid of the half paid summary on top in accordance to month. However, as mentioned in my earlier post, the tricky part is I want to add the unpaid balance of the half paid into the unpaid summary. If possible, I don't want to add additional columns like amount paid, balance unpaid. Maybe the amount paid shall be highlighted in the remark column only and let the formula to pick up the figure from the remark column.

Any advise is highly appreciated.