Thread: Sum multiple values for months

Hello bhavik1,

welcome to the forum.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I think I have a fair idea about what you are trying to achieve, and the solution lies either with a pivot table or with SUMPRODUCT(), but a sample file would do wonders to clear up any questions.

cheers

Hi,

Thanks for the help, I've now attached a dummy workbook but if possible can this be done without Pivot tables, to avoid refreshing problems as the 2 sheets are in reality, in two different workbooks

Given your layout you're really restricted to using SUMPRODUCT, eg:

Code:

B5:
=SUMPRODUCT((Data!$A$6:$A$37=$A5)*(Data!$C$3:$T$3-DAY(Data!$C$3:$T$3)+1=B$3)*(Data!$C$6:$T$37))
applied across matrix B5:N19

Note: you have E101 listed twice (A5 & A10)


I am however curious as to how your source table (Data) is laid out - either you have:

a) numerous "data" sheets to collate
(if so, note that SUMPRODUCT won't work in 3D without INDIRECT at which point it becomes volatile)

b) lots (& lots) of columns with some dates missing (or you're using XL2007)


Either way, with a years worth of data to aggregate I suspect your summary table will be pretty slow to calculate.

I have a lot of data sheets but many only have 10 days in them. Some however overlap 2 months such as the dummy document and that is where I am having problems

First, please do not quote prior posts in full in your response this practice simply clutters up the board...only quote necessary parts so as to maintain logical flow to thread (ie if answering posts out of sequence).


The fact that the dates cross months is of no real / fundamental concern given this can be handled via formulae (as shown by earlier formula).

What should be of real concern to you is the fact that your data is stored in an illogical manner and will result in very inefficient methods for analysis.

If you wish to conduct efficient analysis on mass data with Excel as the data warehouse then you really must strive to store data in a logic and coherent manner
ie think along the lines of database storage.

Using your sample file as a discussion point... and specifically "Data" sheet

IMO you should be storing these data points in a table with the following structure ("columns"):

Code:

Ref | No | Date | Value

each value in your present matrix (C6:T37) would in the above set-up be represented by a separate row / transaction in your listing, eg:

Code:

E101 | 1 | 24-Jun | 12
E101 | 1 | 25-Jun | 12
etc etc...

You will find you can analyse the above table far (far) more efficiently - eg Pivot Table.