Calculating cells based on dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Superchikn - 17 Mar 2006 21:49 GMT

I have 2 columns: one that has dates ranging from 10/03/03 through 12/31/05.
The second has money deposited on the previous columns date. I can not seem
to find the right 'SUMIF' syntax to give me a total of money deposited for a
certain date range. for example how much money deposited in March 2004.

Any help would be greatl appreciated.

- Superchikn

Reply to this Message

Dave Peterson - 17 Mar 2006 22:07 GMT

=sumproduct(--(text(a1:a10,"yyyymm")="200403"),(b1:b10))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

======
You may want to look into using data|pivottable. You can group by the date
field and not have to use separate formulas for each month/year.

> I have 2 columns: one that has dates ranging from 10/03/03 through 12/31/05.
> The second has money deposited on the previous columns date. I can not seem
[quoted text clipped - 4 lines]
>
> - Superchikn

Signature

Dave Peterson

Reply to this Message