# Exel- lookup/subtotal

oo7juk 20:49 24 Sep 09
Locked

Hi,

I'm looking for a formula that will look down a column containg dates and using the sum function total for that particular day.

Example:

ColA could have dates listed as follows:

ColA ColB
01/02/09 £10
05/02/09 £20
01/02/09 £30
06/02/09 £60

Wanting a cell to populate with the total for each day, i.e 01/02/09 = £40

The cells to populate will cover a whole calendar month and hopefully have a total for each day, regardless where it is in the column range.

Thanks,

ashleycardwell94 20:56 24 Sep 09

im not to sure what you are requesting, try more explaining

VoG II 21:12 24 Sep 09

Try

=SUMIF(A:A,"01/02/2009"+0,B:B)

but this seems an ideal candidate for a Pivot Table click here and click here

oo7juk 22:03 24 Sep 09

The sheet already has data copied from a pivot table. The source data was extracted from a system and produce sub totals

When I add additional dates not from system , from a paper source, the dates differ.

In col A I have transaction dates, next col I have figures and then subtotals. These are from pivot table. The col I have dates that I enter manually, which differ from those in Col A.

I want to retain the pivot subtotals and add an additional col totalling the manual dates entered per date group.

Thanks

oo7juk 10:03 25 Sep 09

VoG,

The formula you provided works and is great. When I copy/scroll down formula the date stays the same, how can I copy down for each day.

Thanks,

VoG II 10:33 25 Sep 09

The best way is to use a spare column (C in the example below).

in C1 01/02/2009

in C2 =C1+1

and copy down as far as needed.

Then the sum formula becomes

=SUMIF(A:A,C1,B:B)

copied down as far as needed.

oo7juk 16:20 25 Sep 09

Many thanks VoG and others who replied.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac