Looking for a formula to calculate expenses within a certain month

=SUMPRODUCT(MONTH(D1:D10)=4)*(E1:E10))

or, equally,

=SUM((MONTH(D1:D10)=4)*(E1:E10))

The latter must be entered as an array formula, that is, you need to hit control+shift+enter at the same time (not just enter) to enter it.

Note. 4 is the month number for April.

Aladin

Wow...that is great! Thanks for your help and getting back to me so quickly. Is there any way to get the same results by selecting the entire columns (D:D, E:E) so that I can have Excel pull April out of the spreadsheet, rather than me selecting the range of the April's myself? I tried to substitute the D:D for the D1:D10 and that didn't work.
Gina

pivot tables could also work for you

Hi,
I know you asked for a formula, but a pivot table could also work.

If you had labels in row(1) and col(A) was "date", col(B) was "money" you could make col(C) "month" or "month/year" depending on your needs.

If you want col(C) to be "month", then enter the following into c2 and copy down as needed
=month(a2)
If you want col(C) to be "month/year" then enter the following into c2 and copy down as needed
=month(a2)&"/"year(a2)

then select the entire b and c columns, go to data-->pivot table and pivot chart report, click "next", click "next" again to accept the range, put it in existing worksheet (I picked E1), and BEFORE clicking "finish", go to "Layout", drag the "month" or "month/year" button over to the "row" area and drop it. Then grab the "money" button and drag it over to the "data" area and drop it. If you double click the data button at this point it will give you a list of the available calculations it will perform, pick "sum" if it is not already your default. Then click "OK", then click "finish".

If you add more dates later, just type them in the columns you already have laid out. Then you can right click anywhere on the pivot table and click "refresh" to update the report.

Sheesh, that's a lot more complicated than Aladin's! :/

BTW, you could copy that "month" or "month/date" formula all the way to the last row of the spreadsheet if you can tolerate a "1/1900" row in your pivot table.

Re: pivot tables could also work for you

I agree with Eric that a Pivot Table would work great, but it doesn't have to be quite so complicated. No need to add month and year columns. Just create your pivot table from your existing table and use the Group option to group the dates by month. See help file for examples

With SUMPRODUCT and array formulas you can't indeed refer to whole columns as ranges. The solution is to use a named dynamic range.

Activate D1 (assuming that it contains the first date).

Activate the option Insert|Name|Define.

Enter DATES for Names in Workbook.

Enter the following for Refers To:

=OFFSET(x!$D$1,0,0,count($D:$D),1)

Follow the same proc to define AMOUNTS for column E values.

Change the SUMPRODUCT formula as follows:

=SUMPRODUCT(MONTH(DATES)=4)*(AMOUNTS))

Aladin

=============

? Not working for me

Lenze, I'd appreciate it if you could help me figure out why I can't get Excel to perform that useful function.

Following along with the original example in this post, let's say we have three months with four days each, all with associated money entries. Creating a pivot on those two columns basically gives me the same list again since the months are separated out by their individual date entries. Following the instructions in the help file, i right click on the field that contains the date items and select "group and outline"-->"group" (I get no "starting at" or "ending at" box). Excel reports back "cannot group that selection". If I manually highlight the date column in the pivot table report it gives me one giant group with all of the dates, if i manually select just the dates in 1 month, then it groups those, but that's pretty inconvenient, and still doesn't sum the values for that month.

Thanks for your attention

Check your email

Thx Lenze, you fixed it for me!

Since I'd selected the entire column when I made the pivot table, the bottom row of the report was a "blank", and effectively prevented me from using the "group" function. Works fine when I limit the pivot table selection to just the portions of the column for which data has already been entered.