Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with multiple criteria
Ok, here's one for the advanced users.
I have a sheet that is being totalled by month with the following catagories: I have to sum a column if multiple criteria are met. First, I need to take into account the status (awarded, submitted, lost, etc.), then the sum must look to see if those amounts are within a certain month. any ideas on using multiple criteria within a sumif statement? Brent M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with multiple criteria
You need to use an array formula that incorportes SUM and
IF (but is not the same as SUMIF) or use SUMPRODUCT. Based on what you've posted this should start you in the right direction: A1:A100 - dates B1:B100 - status C1:C100 - values to sum To sum all "lost" values for the month of April (assuming year doesn't matter), try: =SUMPRODUCT((TEXT(A1:A100,"mmm")="Apr")*(B1:B100=" lost") *C1:C100) To learn more, check out Bob Phillips' website: http://www.xldynamic.com/source/xld.SUMPRODUCT.html HTH Jason Atlanta, Ga -----Original Message----- Ok, here's one for the advanced users. I have a sheet that is being totalled by month with the following catagories: I have to sum a column if multiple criteria are met. First, I need to take into account the status (awarded, submitted, lost, etc.), then the sum must look to see if those amounts are within a certain month. any ideas on using multiple criteria within a sumif statement? Brent M . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with multiple criteria
I do similar things with the AutoFilter.......then copying the filtered data
over to a ReportSheet, and then doing SubTotals on that sheet............ hth Vaya con Dios, Chuck, CABGx3 "brentm" wrote in message ... Ok, here's one for the advanced users. I have a sheet that is being totalled by month with the following catagories: I have to sum a column if multiple criteria are met. First, I need to take into account the status (awarded, submitted, lost, etc.), then the sum must look to see if those amounts are within a certain month. any ideas on using multiple criteria within a sumif statement? Brent M |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with multiple criteria
You might also look at Data=PivotTable Report.
You might look at Jon Peltier's site. http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Regards, Tom Ogilvy "brentm" wrote in message ... Ok, here's one for the advanced users. I have a sheet that is being totalled by month with the following catagories: I have to sum a column if multiple criteria are met. First, I need to take into account the status (awarded, submitted, lost, etc.), then the sum must look to see if those amounts are within a certain month. any ideas on using multiple criteria within a sumif statement? Brent M |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF with multiple criteria
Have you tried an array entered sum formula with multiple criteria?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with Multiple Criteria | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMIF and {multiple criteria} | Excel Discussion (Misc queries) | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
SUMIF with multiple criteria | Excel Worksheet Functions |