Sum up yearly data from various date

[tantcu]

Hi guys,

I'm having a data table that contains various data from different date. I would like to sum all the data into the summary sheet which consolidate into yearly data. PLease help me out if you know how to come up with the formula like that. I'm trying INDEX, MATCH and SUMIFS functions but I don't know how to put all these formulas into one.

Attached is the file. SAMPLE.xlsx

[BB1972]

tantcu,

I'm fairly certain I can help you here, but before I do so, would you mind uploading your spreadsheet again, after removing what appear to be all "0" values between columns E to DM. I'm working on a laptop here, but even if I wasn't, uploading a sheet with over 100 columns which only appear to contain zero values before getting to your data is no encouragement to assist you.

[oeldere]

See if the attached file (with VBA) is what you're up to.

The macro is ready if the cursor is in cell A (on the sheet output).

Please reply.

[tantcu]

Hello oeldere, thank you for the macro. I tried to run the macro code again and it reported error for typing mismatch and direct debuger to this line " If Sheets("output").Range("E" & rsht2).Value = 0 Then". Do you know what the problem is?

[tantcu]

tantcu,

I'm fairly certain I can help you here, but before I do so, would you mind uploading your spreadsheet again, after removing what appear to be all "0" values between columns E to DM. I'm working on a laptop here, but even if I wasn't, uploading a sheet with over 100 columns which only appear to contain zero values before getting to your data is no encouragement to assist you.

Hi BB1972, sorry for a lot of 0 values, I uploaded those data directly from the database so it results a lot of 0s filling in empty blanks. Did you come up with the formula or macro code? BTW the spreadsheet is supposed to have these 0s, can you include those 0s in the calculation?

[BB1972]

Hi tanctu

Hi BB1972, sorry for a lot of 0 values, I uploaded those data directly from the database so it results a lot of 0s filling in empty blanks.

I understand, however a sample sheet would generally contain a small, manageable sample of data. My solution involves using a formula only, and you'll be able to adjust the ranges to suit your entire data range when you apply it to your master sheet.

[tantcu]

This is a new spreadsheet. Thank you for helping. SAMPLE.xlsx

[tantcu]

Hi tanctu



I understand, however a sample sheet would generally contain a small, manageable sample of data. My solution involves using a formula only, and you'll be able to adjust the ranges to suit your entire data range when you apply it to your master sheet.

This is a new spreadsheet. Thank you for helping Attachment 209029[/QUOTE]

[tantcu]

Hey guys, the post still did not have final solution yet. Could anyone know the formula for this? I really need this help. Thanks

[oeldere]

Below the macro I used.

I don't get any error.

See the attached file.

Please Login or Register  to view this content.