HELP!!!!

[Wiggy]

I know that this is probably a simple fomula to write, but I'm having no luck
at all in getting it to do what i want.
Sheet1, col i is a sales figure
Sheet1, col L is a sales date
Sheet2, col C is the monthly sales figure


I want to automatically add the sales into the right monthly figures
without having to do it manually, bareing in mind that sheet 1 may not be
entered in a chronological order.
Basically, if L6=(a date in june) add i6 to Sheet2 c3
if L12=( date in june) add i12 to sheet2 c3
if L9=(a date in july) add i9 to heet to c4
Anyone any ideas?? I've started to pull my hair out!!

 

[Fergal]

Hi Wiggy - not sure Im getting your problem, but you can try to extract month
from your dates and use sumif or consolidate thereafter. Depending on date
format, you can use MID(cell ref, start cell,len of characters) - ie -
06/05/2008 = mid(a1,4,2) - might want to add AND and LEN to your formula for
date anomalies!!!

 

[Pete_UK]

If you put the numbers 1 to 12 in cells B2:B13 on Sheet2 (representing
the months), then you can put this in C2:

=SUMPRODUCT((Sheet1!L$1:L$200=B2)*(Sheet1!i$1:i$200))

and copy it down. It will give you January's total, then February's,
then March etc.

If you want to start with June's figures, put 6 - 12 then 1 - 5 in
B2:B13.

Hope this helps.

Pete

 

[Sandy Mann]

If your dates are real XL dates then in Sheet2 Cell C3 enter the formula:

=SUMPRODUCT((MONTH(Sheet1!L2:L200)=6)*(Sheet1!I2:I200))

For July in C4 change the (MONTH(Sheet1!L2:L200)=6) to
(MONTH(Sheet1!L2:L200)=7)

If the data extends over more than one year then use:

=SUMPRODUCT((MONTH(Sheet1!L2:L200)=6)*(YEAR(Sheet1!L2:L200)=2008)*(Sheet1!I2:I200))

for January, where the formula would take an empty cell as being a January
date use:

=SUMPRODUCT((Sheet1!L2:L200<>"")*(MONTH(Sheet1!L2:L200)=6)*(YEAR(Sheet1!L2:L200)=2008)*(Sheet1!I2:I200))

or

=SUMPRODUCT((Sheet1!L2:L200<>"")*(MONTH(Sheet1!L2:L200)=6)*(Sheet1!I2:I200))

without checking the year.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk