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

1. ### White StormyTake that, subspace!

Joined:
Sep 17, 2002
Messages:
85,489
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. ### mobbarleyActive Member

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

3. ### 5Gen_PreludeThere might not be an "I" in the word "Team", but

Joined:
Mar 14, 2000
Messages:
14,519
1
Location:
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 StormyTake that, subspace!

Joined:
Sep 17, 2002
Messages:
85,489
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 StormyTake that, subspace!

Joined:
Sep 17, 2002
Messages:
85,489
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..

Joined:
Sep 17, 2002
Messages:
85,489
70
Location:
Sparkopolis
7. ### 5Gen_PreludeThere might not be an "I" in the word "Team", but

Joined:
Mar 14, 2000
Messages:
14,519
1
Location:
Sooo... did you figure it out?

8. ### White StormyTake that, subspace!

Joined:
Sep 17, 2002
Messages:
85,489
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 StormyTake that, subspace!

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

10. ### White StormyTake that, subspace!

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

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

Joined:
Mar 14, 2000
Messages:
14,519