Help with VBA quarterly, 6-month and yearly report

[lucianogh]

Hey guys, how are you?

This is my first post here, so I'm sorry if my doubt is too simple for you guys haha. I really don't know much about VBA and have just started studying it.

Anyway, let's go to my "problem". What I'm trying to do here is generating quarterly, 6-month or yearly reports based on monthly series of data. As you will see in my sample file, I have all different variables in the rows, and the different months in the columns.

What's important here is that I must be able to choose on which row the data begins and which rows it ends at, and also in which month (column) I want to begin and end the report. I don't care if I have to do that "inside" the code, that wouldn't be a problem as no one else is going to use the spreadsheet. Also, I have to keep skipping the rows which are blank. Find in the Yearly, 6-month and Quarterly tabs the kind of report I expect to generate. I also don't need to generate them all at the same time.

I know I'm able to do that with formulas, as I was doing so far, but as I've been needing to do it a lot, I'm sure that it's making me waste too much time.

Thanks in advance and anxiously waiting for you help,

Luciano

 

[Smallman]

Hi lucianogh

Welcome to the chandoo forum.

The first thing you need to do is to get rid of the text headers. These make it very difficult to sum, quarters, 6 months and years. Once you add dates as your headings summing between the dates becomes reasonably straight forward.

Here is an example for you to build on

put

Dates as your headers of Row 1 of Data tab.

1 Jan 14
1 Feb 14
etc

Now in the Yearly tab in B1 put
1 Jan 14

and C1 put

1 Jan 15

Now in B2 put this

=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$B$1:$Q$1>=$B$1)*(Data!$B$1:$Q$1<$C$1))

That will get you started now just apply the same theory to the rest of the file.


Take care

Smallman

 

[lucianogh]

Hey @Smallman thanks for the help. Idk why, but that formula is not working properly. The result is supposed to be 53, but with this formula I get 12. See the attached file.

Anyway, is there any simple way to do this with VBA?

Thanks again!

 

[Smallman]

You are correct. I forgot to put the last part of the formula on.

=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(Data!$B$1:$Q$1>=$B$1)*(Data!$B$1:$Q$1<$C$1)*(Data!$B$2:$Q$10))

That should see you right.
Smallman