Hi all, thanks in advance. I have two questions.
I need to add values in columns based on dates that may or may not have
entries on consecutive days and possibly have entries on multiple rows using
the same date.
Date Value 1 Value 2
1/1/09 1 $150.00
1/6/09 2 $0.44
2/1/09 1 $50.00
2/1/09 2 $10.00
2/11/09 1 $100.00
The totals will be used in a report that has totals from other data for each
workday of the month.

Can I create dynamic named range? The number of entries vary each month.
and I don't want the calculation to check the entire column, only cells with
entries.

Thanks again
Dave

Hi Dkingfish,

Assuming that your data is in range A1:C1000 and start date is in cell E1
and end date is in cell F1 and results you want is in cell F1 pl put
following formula in cell F1 and in G1.



=SUMPRODUCT((A1:A1000=E1)*(A1:A1000<=F1)*(B1:B100 0))


=SUMPRODUCT((A1:A1000=E1)*(A1:A1000<=F1)*(C1:C100 0))


Regards

H S Shastri

Pl do not forget to PRESS "YES" BUTTON if post found useful.


+++++++++++++++++++++++++++++++++++++++++++

"dkingfish" wrote:

Hi all, thanks in advance. I have two questions.
I need to add values in columns based on dates that may or may not have
entries on consecutive days and possibly have entries on multiple rows using
the same date.
Date Value 1 Value 2
1/1/09 1 $150.00
1/6/09 2 $0.44
2/1/09 1 $50.00
2/1/09 2 $10.00
2/11/09 1 $100.00
The totals will be used in a report that has totals from other data for each
workday of the month.

Can I create dynamic named range? The number of entries vary each month.
and I don't want the calculation to check the entire column, only cells with
entries.

Thanks again
Dave

Hi,

I'm not clear on your question, but if all you want is sum by date

=SUMIF(A$1:A$1000,F1,C$1:C$1000)

where a date is entered in F1. Or a set of dates in F1:F5

Regarding dynamic range names, suppose column a contains a date if and only
if there is data to be summed, then choose Inset, Name, Define and enter
MyDates in Name in workbook, and in the Refers to box enter

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))

Adjust as necessary.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"dkingfish" wrote:

Hi all, thanks in advance. I have two questions.
I need to add values in columns based on dates that may or may not have
entries on consecutive days and possibly have entries on multiple rows using
the same date.
Date Value 1 Value 2
1/1/09 1 $150.00
1/6/09 2 $0.44
2/1/09 1 $50.00
2/1/09 2 $10.00
2/11/09 1 $100.00
The totals will be used in a report that has totals from other data for each
workday of the month.

Can I create dynamic named range? The number of entries vary each month.
and I don't want the calculation to check the entire column, only cells with
entries.

Thanks again
Dave

Thanks for your help. Since I posted I was able to get a Sumproduct formula
to get the results I need. I was using a + or * to join the agruement
instead of a comma. Once again, user error.

Thanks for the help with the dynamic range name too.

Dave

"dkingfish" wrote:

Hi all, thanks in advance. I have two questions.
I need to add values in columns based on dates that may or may not have
entries on consecutive days and possibly have entries on multiple rows using
the same date.
Date Value 1 Value 2
1/1/09 1 $150.00
1/6/09 2 $0.44
2/1/09 1 $50.00
2/1/09 2 $10.00
2/11/09 1 $100.00
The totals will be used in a report that has totals from other data for each
workday of the month.

Can I create dynamic named range? The number of entries vary each month.
and I don't want the calculation to check the entire column, only cells with
entries.

Thanks again
Dave