How do I add amounts referencing specific dates?

[Stinky]

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[Mike H]

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Stinky]

I've tried it exactly as you suggest, Mike, but unfortunately get a 0 return
in each cell. I was probably also not very specific. I'd really like only
to return a value if there's a positive result against a date. If there is a
positive result and let's say that's in D2, I'd then like to be able to
return the relevant date in C2. So for the data set below, I'd end up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[Mike H]

Hi,

Setting aside for a moment that my first suggestion wasn't what you wanted
then 'provided' you enetered the formula as posted if it returned all zeroes
then i suspect there's something wrong with your data.

Are you sure those dates are really dates or are they text that look like
dates. Likewise for the numbers.

Test them with this

=isnumber(a2)
=isnumber(b2)

Drag down and it should return TRUE if the dates and numbers are real numbers.

However, does this do what you want. Put the formula in C2 and drag down.
Every time the date changes in column A it will sum the date

=IF(A2<>A3,SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100)),"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

I've tried it exactly as you suggest, Mike, but unfortunately get a 0 return
in each cell. I was probably also not very specific. I'd really like only
to return a value if there's a positive result against a date. If there is a
positive result and let's say that's in D2, I'd then like to be able to
return the relevant date in C2. So for the data set below, I'd end up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[Stinky]

You were right, Mike, it must have been seeing the date as text - when I
re-entered the dates it worked. The second one works too - so thanks for
those. What you've given me is certainly better than I had before, but not
quite there. I'm not sure what I'm trying to achieve is really possible.
I'm trying to avoid having the data I need in a column adjacent to the B
column. I really want to be able to put the data in my original example
(which is only part of a data set) from about row 7 downwards, and above that
caputre the summary data as per my last posting. Eg

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299


07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279

The reason being that I need the summary data at the top because the source
data below can run to pages and pages with many entries for each day (I've
just shortened it to use as an example) and I don't want to run to pages and
pages to see each day's summary.

Hope I'm explaining it correctly.

Hi,

Setting aside for a moment that my first suggestion wasn't what you wanted
then 'provided' you enetered the formula as posted if it returned all zeroes
then i suspect there's something wrong with your data.

Are you sure those dates are really dates or are they text that look like
dates. Likewise for the numbers.

Test them with this

=isnumber(a2)
=isnumber(b2)

Drag down and it should return TRUE if the dates and numbers are real numbers.

However, does this do what you want. Put the formula in C2 and drag down.
Every time the date changes in column A it will sum the date

=IF(A2<>A3,SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100)),"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

I've tried it exactly as you suggest, Mike, but unfortunately get a 0 return
in each cell. I was probably also not very specific. I'd really like only
to return a value if there's a positive result against a date. If there is a
positive result and let's say that's in D2, I'd then like to be able to
return the relevant date in C2. So for the data set below, I'd end up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


:

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[Mike H]

Hi,

The second bit is easy. Extract a list of the unique dates using

Data|Filter|Advanced filter and follow the wizard for extracting unique
values.

When you've got the unique values(dates) use the first formula I gave you
changed to look like this

=SUMPRODUCT(($A$2:$A$100=G2)*($B$2:$B$100))

Assuming the first of your unique dates are in G2
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

You were right, Mike, it must have been seeing the date as text - when I
re-entered the dates it worked. The second one works too - so thanks for
those. What you've given me is certainly better than I had before, but not
quite there. I'm not sure what I'm trying to achieve is really possible.
I'm trying to avoid having the data I need in a column adjacent to the B
column. I really want to be able to put the data in my original example
(which is only part of a data set) from about row 7 downwards, and above that
caputre the summary data as per my last posting. Eg

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299


07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279

The reason being that I need the summary data at the top because the source
data below can run to pages and pages with many entries for each day (I've
just shortened it to use as an example) and I don't want to run to pages and
pages to see each day's summary.

Hope I'm explaining it correctly.

Hi,

Setting aside for a moment that my first suggestion wasn't what you wanted
then 'provided' you enetered the formula as posted if it returned all zeroes
then i suspect there's something wrong with your data.

Are you sure those dates are really dates or are they text that look like
dates. Likewise for the numbers.

Test them with this

=isnumber(a2)
=isnumber(b2)

Drag down and it should return TRUE if the dates and numbers are real numbers.

However, does this do what you want. Put the formula in C2 and drag down.
Every time the date changes in column A it will sum the date

=IF(A2<>A3,SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100)),"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

I've tried it exactly as you suggest, Mike, but unfortunately get a 0 return
in each cell. I was probably also not very specific. I'd really like only
to return a value if there's a positive result against a date. If there is a
positive result and let's say that's in D2, I'd then like to be able to
return the relevant date in C2. So for the data set below, I'd end up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.



:

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


:

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[Stinky]

OK thanks Mike - got it, and thanks so much for your help. I was hoping that
I could do it automatically because the spreadsheet has a lot of users, and I
wanted to get the summary data each day without others having to manipulate
the spreadsheet. The data would be summed automatically and by setting the
print area, they could just print off the report each day............

Hi,

The second bit is easy. Extract a list of the unique dates using

Data|Filter|Advanced filter and follow the wizard for extracting unique
values.

When you've got the unique values(dates) use the first formula I gave you
changed to look like this

=SUMPRODUCT(($A$2:$A$100=G2)*($B$2:$B$100))

Assuming the first of your unique dates are in G2
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

You were right, Mike, it must have been seeing the date as text - when I
re-entered the dates it worked. The second one works too - so thanks for
those. What you've given me is certainly better than I had before, but not
quite there. I'm not sure what I'm trying to achieve is really possible.
I'm trying to avoid having the data I need in a column adjacent to the B
column. I really want to be able to put the data in my original example
(which is only part of a data set) from about row 7 downwards, and above that
caputre the summary data as per my last posting. Eg

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299


07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279

The reason being that I need the summary data at the top because the source
data below can run to pages and pages with many entries for each day (I've
just shortened it to use as an example) and I don't want to run to pages and
pages to see each day's summary.

Hope I'm explaining it correctly.

Hi,

Setting aside for a moment that my first suggestion wasn't what you wanted
then 'provided' you enetered the formula as posted if it returned all zeroes
then i suspect there's something wrong with your data.

Are you sure those dates are really dates or are they text that look like
dates. Likewise for the numbers.

Test them with this

=isnumber(a2)
=isnumber(b2)

Drag down and it should return TRUE if the dates and numbers are real numbers.

However, does this do what you want. Put the formula in C2 and drag down.
Every time the date changes in column A it will sum the date

=IF(A2<>A3,SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100)),"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


:

I've tried it exactly as you suggest, Mike, but unfortunately get a 0 return
in each cell. I was probably also not very specific. I'd really like only
to return a value if there's a positive result against a date. If there is a
positive result and let's say that's in D2, I'd then like to be able to
return the relevant date in C2. So for the data set below, I'd end up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.



:

Hi,

Let's say we have Column A containing dates in April 2010 and data to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


:

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date, given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a training
session in 2 weeks to learn Pivot Tables!]

 

[RagDyeR]

One way to create an "automatic" totaling location in your report would be
start off with a list of pertinent dates at the top.
For example, if you're only interested in weekdays, list the 20 or so days
say in Column A (TRUE XL dates), and then enter something like this in the
adjacent rows in Column B:

=Sumif(A$30:A$500,A1,B$30:B$500
(Adjust your ranges)
And copy down as far as needed.

Then, just set your print area to encompass the days that have so-far
already elapsed in the month.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


OK thanks Mike - got it, and thanks so much for your help. I was hoping
that
I could do it automatically because the spreadsheet has a lot of users, and
I
wanted to get the summary data each day without others having to manipulate
the spreadsheet. The data would be summed automatically and by setting the
print area, they could just print off the report each day............

Hi,

The second bit is easy. Extract a list of the unique dates using

Data|Filter|Advanced filter and follow the wizard for extracting unique
values.

When you've got the unique values(dates) use the first formula I gave you
changed to look like this

=SUMPRODUCT(($A$2:$A$100=G2)*($B$2:$B$100))

Assuming the first of your unique dates are in G2
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

You were right, Mike, it must have been seeing the date as text - when I
re-entered the dates it worked. The second one works too - so thanks
for
those. What you've given me is certainly better than I had before, but
not
quite there. I'm not sure what I'm trying to achieve is really
possible.
I'm trying to avoid having the data I need in a column adjacent to the B
column. I really want to be able to put the data in my original example
(which is only part of a data set) from about row 7 downwards, and above
that
caputre the summary data as per my last posting. Eg

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299


07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279

The reason being that I need the summary data at the top because the
source
data below can run to pages and pages with many entries for each day
(I've
just shortened it to use as an example) and I don't want to run to pages
and
pages to see each day's summary.

Hope I'm explaining it correctly.

Hi,

Setting aside for a moment that my first suggestion wasn't what you
wanted
then 'provided' you enetered the formula as posted if it returned all
zeroes
then i suspect there's something wrong with your data.

Are you sure those dates are really dates or are they text that look
like
dates. Likewise for the numbers.

Test them with this

=isnumber(a2)
=isnumber(b2)

Drag down and it should return TRUE if the dates and numbers are real
numbers.

However, does this do what you want. Put the formula in C2 and drag
down.
Every time the date changes in column A it will sum the date

=IF(A2<>A3,SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100)),"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


:

I've tried it exactly as you suggest, Mike, but unfortunately get a
0 return
in each cell. I was probably also not very specific. I'd really
like only
to return a value if there's a positive result against a date. If
there is a
positive result and let's say that's in D2, I'd then like to be able
to
return the relevant date in C2. So for the data set below, I'd end
up with:

07/04/2010 345
08/04/2010 1059
09/04/2010 1530
12/04/2010 1299 etc

Sorry! It's a little bit complicated.



:

Hi,

Let's say we have Column A containing dates in April 2010 and data
to sum in
column B. Put this in a cell and it sums 1 April 2010. Drag down
for 2 April
2010 etc

=SUMPRODUCT(($A$2:$A$100=DATE(2010,4,ROW(A1)))*($B$2:$B$100))


--
Mike

When competing hypotheses are otherwise equal, adopt the
hypothesis that
introduces the fewest assumptions while still sufficiently
answering the
question.


:

If I have the following data:

Date No
07/04/2010 170
07/04/2010 175
08/04/2010 170
08/04/2010 170
08/04/2010 719
09/04/2010 1190
09/04/2010 340
12/04/2010 1020
12/04/2010 279


How do I sum up the no to give one value against each date,
given that the
list of dates could be any date in the month?

[Hopefully my final post for this project and I have booked a
training
session in 2 weeks to learn Pivot Tables!]