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