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!]

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" wrote:

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!]

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.



"Mike H" wrote:

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" wrote:

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!]

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$10 0)),"")
--
Mike

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


"Stinky" wrote:

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.



"Mike H" wrote:

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" wrote:

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!]

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.


"Mike H" wrote:

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$10 0)),"")
--
Mike

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


"Stinky" wrote:

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.



"Mike H" wrote:

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" wrote:

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!]

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.


"Stinky" wrote:

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.


"Mike H" wrote:

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$10 0)),"")
--
Mike

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


"Stinky" wrote:

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.



"Mike H" wrote:

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" wrote:

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!]

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............

"Mike H" wrote:

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.


"Stinky" wrote:

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.


"Mike H" wrote:

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$10 0)),"")
--
Mike

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


"Stinky" wrote:

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.



"Mike H" wrote:

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" wrote:

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!]