Show the SUM of a list of numbers onto another sheet

[twisted31]

I need to show the sum from a list of numbers i get everyday onto another sheet. the only problem is I get a different amount of numbers each day.

For example, one day i might get

1
1
1
2

Then the next day i might only get

5
3

I cant color code it because the number of lines will change depending on how many numbers i get, so i'll have to delete/insert lines, which also means i cant make it show the SUM of all those numbers on a seperate sheet by using every nth cell because it will always be different.

How could i make it so that on another sheet, it shows the sum of however many numbers i get that day, even if its always different?


One thing i've noticed is, if on the seperate sheet, if i do =D10 (or whatever cell has the SUM), then insert/delete a line, D10 will change to D9/D10 or whatever the correct cell is, except i dont want to have to =D10, =D18, =D26 , and so on, because that will take a long time. Maybe theres a way to Do the =D10 thing all the way down?

[jeffreybrown]

Why can't you just use >> =SUM(Sheet1!A:A)?

[twisted31]

Why can't you just use >> =SUM(Sheet1!A:A)?

I could do that, and it does change whenever i insert/delete lines, but how would i make it continue?

For example, I did:

=SUM(Sheet1!D3:D11)

but when i drag it down on the other sheet to continue the same thing, instead of

=SUM(Sheet1!D12:20), it does =SUM(Sheet1!D4:D12)

Is there any way to fix that?

EDIT: I actually need it to skip a line, THEN add, like after =SUM(Sheet1!D3:D11) , go to =SUM(Sheet1!D13:D21) , and keep adjusting if i delete/insert lines, if thats possible.

[jeffreybrown]

Having a very hard time seeing your requirements.

Could you add a sample file with a clear before and after?

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[benishiryo]

hi twisted31, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

if you had followed Jeff's method & referred to the whole column, it shouldnt change when you drag the formula down. I did an example in Sheet2 (cell C2 & C3).

but according to your new requirement, you want the 1st sum to be in D3:D11 & then skip 10 cells down, see E2.

[twisted31]

Thanks for the quick replies Jeff.

I've attached a sample.

In the sheet "After1" I have data entered, and in "After2" I need it to show the data from After1 summed up. As I said before the number of lines will vary for each date. Sorry if I'm not explaining too good, hopefully this helps.




Thank you.



EDIT:
benishiryo, thanks for the reply. Unfortunately if I add a line to the list of values in your workbook, it removes the value from the SUM on the other sheet.

[benishiryo]

this is a whole different story with a date condition.
try this in B2 of "After 2":
=SUMIF(After1!$A$3:$A$1104,A2,After1!$B$3:$B$1104)

you do realise that some of your dates are in 2012 & some in 2013 right?

i dont think you want to ignore the years, but to get your answers stated, it's:
=SUMPRODUCT((TEXT(After1!$A$3:$A$1104,"ddmm")=TEXT(A2,"ddmm"))*(After1!$B$3:$B$1104))

[twisted31]

this is a whole different story with a date condition.
try this in B2 of "After 2":
=SUMIF(After1!$A$3:$A$1104,A2,After1!$B$3:$B$1104)

you do realise that some of your dates are in 2012 & some in 2013 right?

i dont think you want to ignore the years, but to get your answers stated, it's:
=SUMPRODUCT((TEXT(After1!$A$3:$A$1104,"ddmm")=TEXT(A2,"ddmm"))*(After1!$B$3:$B$1104))

That worked perfectly. Now i can add or delete however many lines I need since its based off the date. Thank you so much for your help, you're a life saver!