Sum two cells above a specific cell in different two sheets

[mozaya]

I have two sheets:

1: Productivity in different types for 200 labors as follow:

	Date	1	2	3
Labor 1	Prod. 1	10	7	9
	Prod. 2	2	5	0
	Villa no.	591-G	340-F	670-R
Labor 2	Prod. 1	11	0	14
	Prod. 2	3	8	2
	Villa no.	340-F	591-G	591-G

<tbody>
</tbody>

2: The progress sheet for 1000 villa with three floors which i need to link with the first one that any quantity done in the same villa shall be updated directly on the second sheet:

Villa no.	Executed quantity
591-G	????
340-F	????
670-R	????

<tbody>
</tbody>

i want a formula makes me sum all the quantity don by any labor in the same villa in the first sheet to be sum and filled directly in the second sheet in the executed quantity

 

[baitmaster]

Re: very complicated formula to sum two cells above a specific cell in different two sheets

welcome to the board

So if I understand this right, the correct answer in your example would be [591-G] = 10+2 + 0+8 + 14+2 = 36? Because you need to find the corresponding Prod 1 and Prod 2 entries for each instance of the Villa.

That would be a simple SUMIF, but you want to do this for 200 or even 1000 rows in each calculation, making SUMIF impractical?

 

[mozaya]

Re: very complicated formula to sum two cells above a specific cell in different two sheets

right, but what is the other solution instead of that.
that is the question.

 

[baitmaster]

Re: very complicated formula to sum two cells above a specific cell in different two sheets

the fundamental problem here is badly laid-out data. If you have the option to reconstruct it so it is like a database with columns "Villa" "Labor" "date" "value" then it would be much easier. You can use VBA to quickly do that reconstruction. Without it the formula will be difficult to write

I can demonstrate a UDF approach as follows, which creates a bespoke formula for you to use in your spreadsheet:

Option Explicit
Function sumVilla(rngData As Range, rngVillaName As Range)
Dim rngVillas As Range, cl As Range, intCount As Integer

' identify all cells containing the search term
For Each cl In rngData.SpecialCells(xlCellTypeConstants, 2)
    If cl = rngVillaName Then
        If rngVillas Is Nothing Then
            Set rngVillas = cl
        Else
            Set rngVillas = Union(rngVillas, cl)
        End If
    End If
Next cl
    
' extract values from corresponding cells
If rngVillas Is Nothing Then
    sumVilla = 0
Else
    For Each cl In rngVillas
        sumVilla = sumVilla + cl.Offset(-2, 0) + cl.Offset(-1, 0)
    Next cl
End If
End Function

There are 2 problems with this code:
- its slower than a standard Excel formula, and has noticeable calculation time on just the data shown in post 1, let alone 1000 villas
- it requires macros to be enabled in order to give a result

I haven't optimised it yet and can definitely improve the speed, but I'd be looking to restructure the data if at all possible

 

[baitmaster]

ADVERTISEMENT

Re: very complicated formula to sum two cells above a specific cell in different two sheets

you need to post this code in a standard VBA code module, and then write a formula in a cell, =sumvilla([your data table],[range containing villa name])

 

[baitmaster]

Re: very complicated formula to sum two cells above a specific cell in different two sheets

this is a faster version of the same code. It will work better on large data sets as it uses Excel's FIND (Ctrl + F) feature to identify only those search terms of interest and ignore all other cells. It will still show a noticeable lag on calculations

Function sumVilla(rngData As Range, rngVillaName As Range)

Dim cl As Range, clFirst As Range

' identify all cells containing the search term
On Error Resume Next ' in case none exist
    Set clFirst = rngData.Find(What:=rngVillaName, After:=rngData.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) ' find first term
On Error GoTo 0

If clFirst Is Nothing Then Exit Function
Set cl = clFirst ' identify first cell

Do ' keep finding next term until first one found again, at which point exit loop
    sumVilla = sumVilla + cl.Offset(-2, 0) + cl.Offset(-1, 0) ' add current offset values
    
    Set cl = rngData.Find(What:=rngVillaName, After:=cl, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) ' find next instance of term
    
    If cl.Address = clFirst.Address Then Exit Function ' if back at first cell then exit loop
    
Loop
End Function

 

[admiral100]

ADVERTISEMENT

Hi,

Assuming that the structure does not change ....

F4=IF(B4="Villa no.",SUMPRODUCT((C4:E4=$F$1)*(C2:E3)),0)
G4=IF(B4="Villa no.",SUMPRODUCT((C4:E4=$G$1)*(C2:E3)),0)
H4=IF(B4="Villa no.",SUMPRODUCT((C4:E4=$H$1)*(C2:E3)),0)

	A	B	C	D	E	F	G	H
1		Date	10/12/2016	10/12/2016	10/12/2016	340-F	591-G	670-R
2	Labor 1	Prod. 1	10	7	9
3		Prod. 2	2	5	0
4		Villa no.	591-G	340-F	670-R	12	12	9
5	Labor 2	Prod. 1	11	0	14	0	0	0
6		Prod. 2	3	8	2	0	0	0
7		Villa no.	340-F	591-G	591-G	14	24	0
8	Labor 3	Prod. 1	11	0	14	0	0	0
9		Prod. 2	3	8	2	0	0	0
10		Villa no.	340-F	591-G	591-G	14	24	0
11	Labor 4	Prod. 1	34	0	14	0	0	0
12		Prod. 2	3	8	2	0	0	0
13		Villa no.	340-F	670-R	591-G	37	16	8
14	Labor 5	Prod. 1	11	0	14	0	0	0
15		Prod. 2	6	8	2	0	0	0
16		Villa no.	340-F	591-G	591-G	17	24	0
					Total	94	100	17

<tbody>
</tbody>

 

[mozaya]

=IF(Sheet1!B4="villa no.",SUMPRODUCT((Sheet1!C4:E4=$H$3)*(Sheet1!E2:E3)),0)

i want to generate this formula horizontally, so every thing is ok except (Sheet1!E2:E3 can't be generated.
can you send me how to generate it to be (Sheet1!f2:f3),(Sheet1!g2:g3),(Sheet1!h2:h3)

 

[admiral100]

try this
Sheet1!E$2:E$3