How does one get excel sum of a column based on given dates range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

co_jer - 19 Mar 2008 20:17 GMT

in an excel database, there ais one column with multiple dates (some dates
are repeated) and another with related values.

How does one get a sum of related values for a user given date range? What
about averages excluding those with zero values?

Reply to this Message

Gary''s Student - 19 Mar 2008 22:04 GMT

Here is the first part. Say the dates are in column A and the values are in
column B. Then:
=SUMPRODUCT((A1:A100>DATE(2008,3,12))*(A1:A100<DATE(2008,3,24)),(B1:B100))

will give the sum of the values between 3/13/2008 and 3/23/2008

Signature

Gary''s Student - gsnu200774

> in an excel database, there ais one column with multiple dates (some dates
> are repeated) and another with related values.
>
> How does one get a sum of related values for a user given date range? What
> about averages excluding those with zero values?

Reply to this Message

co_jer - 19 Mar 2008 23:12 GMT

Gary"s student. Partial solution is better than none! I wil give it a try.
Thanks much.

Reply to this Message

Gary''s Student - 20 Mar 2008 00:56 GMT

If it is possible, convert the zeros into blanks. AVERAGE() will ignore blanks

Signature

Gary''s Student - gsnu200774

> Gary"s student. Partial solution is better than none! I wil give it a try.
> Thanks much.
[quoted text clipped - 4 lines]
> > How does one get a sum of related values for a user given date range? What
> > about averages excluding those with zero values?

Reply to this Message