Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sumproduct Function To Add Multiple Criteria
Hi,
Can anyone help me. I have an Excel Worksheet that looks like this: Date Type Amount 1-2-06 RENT 250 3-2-06 GOODS 140 5-2-06 RENT 450 6-2-06 TELEPHONES 70 2-3-06 RENT 300 7-3-06 GOODS 220 I want to set up a summary table which adds the values via grouped typed per month, working dynamically as data is added in the above worksheet using the =sumproduct function. I can add the totals per type using: =SUMPRODUCT(($C$3:$C$100)*(B3:B100=E3)) where E3 is RENT but I need to add another criteria using dates for example 1/2/2006= and <=28/2/2006 for Feb. and so forth. For Example my table should look like this: Month RENT GOODS TELEPHONES Feb. March April Thanks Ange |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sumproduct Function To Add Multiple Criteria
=SUMPRODUCT(($C$3:$C$100)*(B3:B100=E3)*(MONTH(A3:A 100)=2)
for Feb -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ange Kappas" wrote in message ... Hi, Can anyone help me. I have an Excel Worksheet that looks like this: Date Type Amount 1-2-06 RENT 250 3-2-06 GOODS 140 5-2-06 RENT 450 6-2-06 TELEPHONES 70 2-3-06 RENT 300 7-3-06 GOODS 220 I want to set up a summary table which adds the values via grouped typed per month, working dynamically as data is added in the above worksheet using the =sumproduct function. I can add the totals per type using: =SUMPRODUCT(($C$3:$C$100)*(B3:B100=E3)) where E3 is RENT but I need to add another criteria using dates for example 1/2/2006= and <=28/2/2006 for Feb. and so forth. For Example my table should look like this: Month RENT GOODS TELEPHONES Feb. March April Thanks Ange |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
sumproduct using multiple criteria | Excel Worksheet Functions | |||
Sumproduct - multiple criteria in Column A | Excel Worksheet Functions |