Excel Gurus, Converge!

You can use SUMIF or SUMIFS with a wildcard * or ? as place holders. So somthing like:

=SUMIF(range, criteria [, sum_range])

=SUMIF(X1:X99,"z*",Y1:Y99)

SumIFs will work, but you'll have to convert the date range to Excel Date code. The wildcard won't work in that format difference.

If you have two other cells you can use to define the date range, it'll be easy. Cell A is lowest date, Cell B is higher date.

= SumIfs(Y1:Y99, X1:X99, ">="&Cell A, X1:X99, "<="&Cell B)

ETA: there's some assumptions, mainly that the dates are actual date format, and not text. If text, the wildcard will work and SumIf only required (assuming you only want to sum the month without multiple years and no other functionality required).

You could always create another row with

=month(x1)

And drag it. Then do

=sumifs(y1:y5000,aa1:aa5000,z)

quote:

---

Yes, the cells are formatted as dates. If it would simplify things, I could stick a row at the top with the month name. It would eliminate the need for using a wildcard.

---

Will you always want to total for only one month? You can use the SumIf or SumIfs, but your date range will non-dynamic. If you include a cell to specify the dates you want to sum between, the above formula works well.

If only the one month, you can use some combination of Datevalue and the number days in the month.

ETA: Datevalue("[month name]"&" 1") will return excel date code for that month in the current year. You can use EOMonth formula R determine the excel code for the last day of that month.

Say column A has the date and column B has the info you want to sum.

Insert a column in between A and B. This will be "Month". So now column A is Date, column B is Month, and column C is Quantity.

Populate column B with =MONTH(A1). Drag and drop to fill the range, let's say it goes down 100 rows.

Now over to the right somewhere (say, column G) create a list of months, 1 through 12.

In column H use the following: =SUMIF(B$1:B$100, G1, C$1:C$100). Drag, drop, and have a beer.

If you didn't figure this out yet, here is the file you want.

LINK