+ Reply to Thread
Results 1 to 4 of 4

Reorganise data into tables with array formulas

  1. #1
    Forum Contributor
    Join Date
    03-09-2009
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007
    Posts
    128

    Reorganise data into tables with array formulas

    Hi,

    The attached document contains one "form" with radiobuttons etc. The idea as that this sheet will be a standalone document for users to fill in. I will receieve all these files and copy the sheet into this attached master document. So in the future the document will have a lot of sheets (might be better off keeping them as seperate files, as I expect about 100+ sheets a year)

    Anyhow, my problem is that I'm trying to aggregate all the data from these sheets by creating a "database" (One row for each sheet). From this database I'm now trying to sort the data into tables that will be the dataset for graphs and statistical calculations.

    In sheet "Summary" you see first a mock-up for the database. And below B18:B30 is the dataset. In C19 I'm trying to make a formula that will count the number of won contracts if it is in january 2010.

    It would be great to get some help on which formula to use in C8:D19.
    Any input on the overall structure of the Excel sheet would also be great
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-09-2009
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Reorganise data into tables with array formulas

    Is there nobody that can help?

  3. #3
    Registered User
    Join Date
    05-01-2009
    Location
    North East (Durham)
    MS-Off Ver
    Excel 2003,7 and 10
    Posts
    63

    Re: Reorganise data into tables with array formulas

    The quickest thing I would do is the following
    Row F
    =MONTH(C3)
    A18-A29 - Numbers 1 to 12 (1 = jan)
    Fomula is C
    {=SUM(IF($F$3:$F$13=A18,$D$3:$D$13))}

    I would also colur the text in F to White so no one can see the numbers - same with A

  4. #4
    Forum Contributor
    Join Date
    03-09-2009
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Reorganise data into tables with array formulas

    Thanks,

    That works perfect

    I ended up with a similar formula:
    =SUM(IF(YEAR($C$3:$C$13)=G4;IF(MONTH($C$3:$C$13)=H4;$D$3:$D$13;0));0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1