How to conditionally Sum across Worksheets

[hawaean]

This seems like a fairly easy operation, but I haven't found a working formula. Also, I know I have layers of complexity, which is why I am turning to you wonderful people for help!

Data Source: On my main tab/sheet, I have a table with a list of Property Names in column 'A'. Moving across, each column represents a year (Year 0, 1, 2, etc.). On separate worksheets, I have a detailed report on each Property Name. The table is filled with letters, indicating in any given year, a property is being (P)urchased, (R)ented, or (S)old.

Ideal Formula: I'd like to have a formula that checks the content of the table cell that corresponds to a certain column. So, if the formula is in column B, I'd like it to look in column B of the table and calculate based on the content, S, R and P (or blank). Then, the formula needs to check the other worksheets for the correct data. So, if the formula is in the "cash flow" row, I need the sum of cash flow for all the active properties.

Method: I used simple "if" statements to accomplish the tasks. For example, for "Profit/Loss:" =IF(C2="P",-'PROP (1)'!$B$18,IF(C2="R",'PROP (1)'!$B$10,IF(C2="S",'PROP (1)'!$B$21,0))). And for "Cash Flow:" =IF(C$2="",0,'PROP (1)'!$B10)+IF(C$3="",0,'PROP (2)'!$B10)+IF(C$4="",0,'PROP (3)'!$B10)

Here's a sample property sheet:

The reason for my request is if I add Prop (4), or let's say 6 more properties, I know there's a better way to put this together. I'd like to have an array or index/match or sumproduct formula that can check the column 'A' names and sum the data by FINDING the corresponding "Prop (x)" sheets.

Can anyone help me with this?

 

[Rhodie72]

This seems like a fairly easy operation, but I haven't found a working formula. Also, I know I have layers of complexity, which is why I am turning to you wonderful people for help!

Data Source: On my main tab/sheet, I have a table with a list of Property Names in column 'A'. Moving across, each column represents a year (Year 0, 1, 2, etc.). On separate worksheets, I have a detailed report on each Property Name. The table is filled with letters, indicating in any given year, a property is being (P)urchased, (R)ented, or (S)old
....

Can anyone help me with this?

I think it would be better to create your own function for this as that is more easily managed with multiple criteria application.

A sample would be like this one:

Function VerseStartPosition(str As String) As Long
' Shorten string to find genesis of verse
    If Len(str) <= byt + Len(Chapter(str)) + 2 Then
        For i = 1 To Len(str)
            Select Case Mid(str, i, 1)
                Case "}"
                    VerseStartPosition = i + 2
                    Exit Function
            End Select
            
        Next i
    Else
        MsgBox "String too long for editing", vbCritical
        VerseStartPosition = 666
    End If
End Function

Private Function Verse(str As String) As String
    Verse = Mid(str, VerseStartPosition(str), Len(str) - VerseStartPosition(str))
End Function

Although these don't apply to your question, I can explain how it works:
1) When you declare the function as private, it won't be available under your usual list of functions as you type, nor will it display the helpful intellitext explaining its usage either.
2) The employment of each element is important as Function works differently to Sub procedures in that functions cannot make changes to sheet elements like subs can.
3) The structure of the name helps you work out exactly how the data I/O (input/output works):
Function VerseStartPosition(str As String) As Long
VerseStartPosition is the bit of data that returns to the calling procedure (it's the output of the function)
str is the data source that you bring into the function for processing.
As String determines the data type that is demanded and if anything else is processed it will generate an error of some sort.

The advantage of creating your own functions is that you can create complex algorithms that process vast amounts of data with a single formula in the spreadsheet. Few people actually do this and limit their time by refusing to explore the possibilities this presents. You could create something that calculates the total capital of the business over every year that the data covers by a simple custom formula like:

"=TotalCapital("C8:L9")"

To show operating capital average on an annual basis. So remember that the incoming data is in the brackets and the outgoing data precedes the brackets. You can have multiple inputs for a function so you can create multiple scenarios for a single ouput and you can reference other functions to drill down in specific conditions.

CAUTION: Long operations can be expected with large amounts of data being recalculated every time the sheets recalculates automatically if you have more than 5000 rows or columns being processed.

 

[FDibbins]

If you use the same headings on each sheet, you can use INDIRECT with a named range (for the sheet names) with SUMPRODUCT(SUMIF())INDEX/MATCH. Yes, it looks complex, but it does what you want. Something like this...

	A​	B​	C​	D​	E​	F​
1​		0​	1​	2​	3​	4​
2​	Prop1
3​	Prop2
4​
5​	aaa	1100​
6​	bbb	2200​
7​	ccc	3300​
8​	ddd	4400​
9​	eee	5500​

B5=SUMPRODUCT(SUMIF(INDIRECT("'"&Properties&"'!A5:A9"),$A5,INDIRECT("'"&Properties&"'!B5:B9")))

I created a named range called Properties - you create a list of all sheet names, then this named range is the list which contains all the sheet names.

Each sheet (I used only 2) looks something like this...

	A​	B​
1​
2​
3​
4​
5​	aaa	100​
6​	bbb	200​
7​	ccc	300​
8​	ddd	400​
9​	eee	500​

Prop2 was the same, except the values where *10

 

[hawaean]

I like and appreciate the idea, but I do have zero VBA knowledge and it might be overkill to learn programming code. Even if you perfectly set this one up, I'd be at a lost if I needed to tweak the sheet.

 

[FDibbins]

And the formula suggested?

 

[hawaean]

Hi Ford,

Let me know if I understand your setup correctly. The B5 formula iooks for the sheet with the name “Properties” and checks that sheet for all the instances of “aaa”. Then it returns the indirect sum of B5:B9. If that's correct, then it's not quite what needs to happen.

Let's simplify and assume that the data that needs to be pulled on the “property” sheets is in the same place. So, if we’re trying to find "aaa", that value will always be in the same cell. In your example, it’s in B5. What I need is for the formula to check the first table, where you have "prop1" and "prop2" and see if something is there or not. To keep things simple, let's say if the B2 cell (Prop1 in Year 0) has an "X", then yes, include. If it has nothing, then don't include that property. In your table example, the result of "aaa" should be 0 since both B2 and B3 are blank. These formulas will be filled across to check future years (0, 1, 2, 3, 4…)

The reason (or motive) behind this is to capture values from the property sheets that are directly dependent on what is happening in the A1:F3 table.

Hopefully this makes sense.

 

[hawaean]

Sorry all. This reply was in RE: the post by Rhodie72.

I like and appreciate the idea, but I do have zero VBA knowledge and it might be overkill to learn programming code. Even if you perfectly set this one up, I'd be at a lost if I needed to tweak the sheet.

 

[FDibbins]

I am having a blonde (grey?) moment

Which sheet is pulling into which sheet?

 

[hawaean]

Hi Ford,

The main sheet contains the table and calculated results. The subject formula checks the table to see what "Prop" sheets to pull data from.

In my example, say we are looking for Cash Flow. The formula would check the table to see which properties are "active". For Cash Flow, that means either a "P" for purchase or "R" for rent. Let's say "Prop1" and "Prop2" both are active. The formula would pull the cash flow in the "Prop1" tab and add it with the cash flow value in "Prop2".

In your example, Prop1 and Prop 2 have blanks, which means the B5 formula should return 0. But I think your formula is not accounting for the table at all and just matches "aaa" with the data in the other sheets.

I am having a blonde (grey?) moment

Which sheet is pulling into which sheet?