Need to write a SUM with IF and AND functions. PLEASE HELP!

[calico2002]

I need help writing a formula. I am trying to make some budgeting sheets in Excel. I have one sheet with a table called MonthlyExpenses, with columns for Date, Categories (includes a drop down list with specific options), and Actual Cost. On another sheet I have a summary sheet which compares monthly estimates with monthly actuals. There is a row for each category (matches the categories in the MonthlyExpenses table). I need a formula that will calculate the actual cost, in each category, for each month. I'm sure it's possible, I'm just not experience enough to get there. To summarize I need a formula with these components (I used examples)

Category=OTEC
If date>=Jan. 1, 2016 AND <=Jan.31,2016
Sum of Actual Cost

Anyone have any ideas on how to put that together? I would really appreciate your help.

 

[etaf]

=sumifs( range to sum of actual costs , range of catergory, "OTEC", range of date , ">=Jan. 1, 2016" , range of date, "<=Jan. 31, 2016")

we can replace the OTEC for the cell with the dropdowns and also change the dates for cells with the range you want to report from

But would need to know the actual ranges of the cells concerned

 

[calico2002]

Ok, so I put in this formula

=SUMIFS(MonthlyExpenses[Actual Cost],MonthlyExpenses[Category],"OTEC", MonthlyExpenses[Date],">=Jan. 1, 2016", MonthlyExpenses[Date],"<=Jan. 31, 2016")

When I pulled the cell ranges, it automatically to the table name [column name], so I assumed that is ok. It accepted the formula, but it's not quite right. On my test data, it should be $1000, and it was giving me 0. Can you find the error?

Thanks for the help, I really have no clue what I'm doing, I just know it's something excel can do.

=sumifs( range to sum of actual costs , range of catergory, "OTEC", range of date , ">=Jan. 1, 2016" , range of date, "<=Jan. 31, 2016")

we can replace the OTEC for the cell with the dropdowns and also change the dates for cells with the range you want to report from

But would need to know the actual ranges of the cells concerned

 

[calico2002]

I've diagnosed it's an issue with the date range part.

 

[etaf]

it may not be picking up the date correctly
are the
MonthlyExpenses[Date]
actually dates

try putting Jan. 1, 2016 and Jan. 31, 2016 into a couple of cells as a date

Assuming Z1 and Z2
=SUMIFS(MonthlyExpenses[Actual Cost],MonthlyExpenses[Category],"OTEC", MonthlyExpenses[Date],">="&Z1, MonthlyExpenses[Date],"<="&Z2)
OR
=SUMIFS(MonthlyExpenses[Actual Cost],MonthlyExpenses[Category],"OTEC", MonthlyExpenses[Date],">="&DATEVALUE("1/1/2016"), MonthlyExpenses[Date],"<="&DATEVALUE("31/1/2016")