Multi criteria Sum

[Salim]

I have excel sheet with 3 columns and i want to add cloumn
3 if column 1 is AS and column 2 is bigger than 30/08/2004.
A1:AS B1:30/09/2004 C1:$20000
A2:AT B2:25/10/2004 C2:$10000
A3:AT B3:2/10/2004 C3:$50000
A4:AS B4:20/07/2004 C4:$12000
A5:As B5:15/06/2004 C5:$8000
A6:AT A6:20/03/2004 c6:$5000
I.E EXPECTED ANSWER IS $20000

 

[Frank Kabel]

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

 

[Aladin Akyurek]

Let Data be the name of the sheet which houses the sample data you posted.

A1:C6 houses the sample.

Activate Insert|Name|Define.
Enter Dtable as name in the Names in Workbook box.
Enter the following in the Refers to box:

=Data!$A$1:INDEX(Data!$C:$C,MATCH(9.99999999999999E+307,Data!$B:$B))

Click OK.

Note that the big number match has been applied to a numeric reference as
required.

The multiconditional formula for summing that you need becomes:

=SUMPRODUCT(--(INDEX(DTable,0,1)=E1),--(INDEX(DTable,0,2)=F1),INDEX(DTable,0,3))

where E1 houses a condition like AS and F1 a date condition like 30/08/2004.

The foregoing is like the one Biff suggested in this thread. The difference
is that the above uses a single dynamic named range.