Sum two cells above a specific cell in different two sheets

mozaya

New Member
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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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?

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.

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:
Code:
``````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

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])

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

Code:
``````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``````

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>

Last edited:
=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)

try this
Sheet1!E\$2:E\$3

Replies
7
Views
159
Replies
8
Views
174
Replies
4
Views
309
Replies
14
Views
208
Replies
1
Views
253

1,196,028
Messages
6,012,956
Members
441,740
Latest member
IammeResources

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back