Spreadsheet question

Discussion in 'OT Technology' started by White Stormy, Oct 18, 2009.

  1. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I have six sheets in a single spreadsheet file.

    I want to make a seventh sheet that acts as a summary for the other six.
    Each sheet has two columns that I'm interested in: a date and a dollar value.

    My goal is to sum the dollar values of all sheets for each month.

    Ex:
    Code:
    [B]Sheet1[/B]
    2009-11-24		$100
    2009-10-18		$250
    2009-10-02		$42
    
    [B]Sheet2[/B]
    2009-11-28		$400
    2009-11-03		$50
    2009-10-05		$316
    
    [B]Sheet3[/B]
    2009-12-03		$25
    2009-11-04		$25
    2009-10-02		$25
    
    and the result would be
    Code:
    [B]Summary[/B]
    2009-12		$525
    2009-11		$325
    2009-10		$373
    
    Each sheet has one row per month, but sometimes the dates don't exactly match exactly the month that the date is meant to represent, so I have to round the dates to find the 'correct' month. I've already done that, so on the Summary sheet, I have the Sheet1 dates corrected to 2009-12-01, 2009-11-01, and 2009-10-01.

    But how do I sum the values from each sheet when the row number for a month in one sheet may not be the same as the row number for that month in another sheet?
     
    Last edited: Oct 18, 2009
  2. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    select all of the columns, use sumif/countif to check if the date is within a criteria?
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Code:
    =SUM((MONTH(Sheet1!A1:A4)=MONTH(Sheet4!A1))*(YEAR(Sheet1!A1:A4)=YEAR(Sheet4!A1))*Sheet1!B1:B4)+SUM((MONTH(Sheet2!A1:A4)=MONTH(Sheet4!A1))*(YEAR(Sheet2!A1:A4)=YEAR(Sheet4!A1))*Sheet2!B1:B4)+SUM((MONTH(Sheet3!A1:A4)=MONTH(Sheet4!A1))*(YEAR(Sheet3!A1:A4)=YEAR(Sheet4!A1))*Sheet3!B1:B4)
    Press CTRL-SHIFT-ENTER after you enter this into Sheet4, B1
     
  4. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I appreciate the effort, but it didn't work and I can't quite make sense of what you were trying to do.

    All of my sheets have names and the columns are not A/B, so I had to replace variables, but I think I did all of that correctly. You were intending for Sheet4 to be the summary, correct?

    the Ctrl+Shift+Enter was doing something with an array? I didn't know it had functions for that at all..

    TIA
     
  5. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    the first part: MONTH(Sheet1!A1:A4)
    just returns the number of the first month in the range

    so how is that any different than just doing: MONTH(Sheet1!A1) ?

    I'm using Google Docs, not Excel, but I don't think anything brought up so far is something that GDocs isn't capable of doing..
     
  6. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Sooo... did you figure it out?
     
  8. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    well I actually found a way to do it using the FILTER function..
    but when the filter function searches and doesn't find something, it returns #N/A (error: no valid data)

    can't add something to #N/A, so if I could turn that #N/A into a zero instead.. it's crazy retarded long, but works.

    this was for B2 in the Summary sheet (B1 is a heading row).. the most recent month total in dollars. the C columns are amounts, B columns are dates. it's a long function, and then I have it in there six times, once for each sheet.
     
  9. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    I think I got it.. I can just wrap each filter function in an IFERROR()
     
  10. White Stormy

    White Stormy Take that, subspace!

    Joined:
    Sep 17, 2002
    Messages:
    85,489
    Likes Received:
    70
    Location:
    Sparkopolis
    :rofl: well it's retarded long, but it works.

    thanks. the array info is what led me to the filter function
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Ookay. I did test it before I posted it though, so maybe it's just a google docs thing.
     

Share This Page