Sum by variable date criteria

• Hello, I have been looking around for a way to do this and haven't quite found what I am looking for. I have a list of entries, each with a date and a number associated with that date. Currently, these dates are stored as Excel dates, and I would like to keep it that way if possible. What I am trying to do is do something like this "=SUMIF(Sheet2!A2:A16, "1/1/2009",Sheet2!B2:B16)", but instead of it needing to be the first of the month, I would like to have any day of the month be valid. However, =SUMIF(Sheet2!A2:A16, "1/*/2009",Sheet2!B2:B16) does not work, likely due to the value actually being a simple date number.

I imagine this is possible using SUM(IF()), but I could not figure it out.

Any help would be greatly appreciated, and I have attached a simple Excel sheet example that might explain better what I am trying to do.

-Kevin

Files

• example.xlsx

• Re: Sum by variable date criteria

shrodekill,

Welcome to Ozgrid.

Your thread title shoud reflect your need and not an anticipated or assumed solution. I have change the title for you. Good thread titles are key to obtainin relevant search results, thus the need to be precise and not assumptive of the answer.

Have you considered a Pivot Table as a possible solution? SUMPRODUCT may also be viable here.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Sum by variable date criteria

Change the Text format to Date format before passing the criteria, and then use the following array formula

{=SUM((MONTH(Sheet2!\$A\$2:\$A\$16)=MONTH(Sheet1!A2))*(Sheet2!\$B\$2:\$B\$16))}

There you go....

• Re: Sum by variable date criteria

Ah, thank you very much AAE. I never thought about using SUMPRODUCT with boolean values before.

=SUMPRODUCT(Sheet2!B2:B16*(MONTH(Sheet2!A2:A16)=*insert month # here*)) is exactly what I needed.

• Re: Sum by variable date criteria

Oh wow, thank you as well SMC for the quick answer. Both of these solutions work wonders.

Thanks again