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