Sum cells with multiple criteria

[Kradores]

Hello,

I try to figure out how to sum from a table right numbers, can't make it work, stuck for a few weeks :

	A	B	C	D	E	F	G
1	Urban	Rural
2	18	13		L1		L	?
3	27	14		L3		H	?
4	72	5		H1
5	32	57
6	4	38		L1
7	-----	------	-	-	-	-	-
8	L	L1	Urban
9	L	L2	Urban
10	L	L3	Rural
11	H	H1	Urban
12	. . .

<tbody>
</tbody>

Explanation:
In table A1:B6 is data from where will be taken all numbers, in column D2:D6 is what need to be calculated.
Result should be in column G2:G3
Starting from row 8 are connections, which mean that in cell G2 should be sum of L1, L2, L3, where L1 is Urban, L2 is Urban and L3 is Rural.
As a result in cell G2 should be 36 (18+14+4).

This is a small example of a big table, thus amount of data is a lot bigger, best would be a formula that can have unlimited number of L# or at least 10, same about Urban/Rural.

Hope to get some answer, thanks a lot in advance!

 

[LloydFinancials]

Hi, Are lines 7 - 12 below your data just for reference in your example? If so, cut lines 7 - 12 and paste to a different sheet, then run this code. If they are always at the bottom of your data, then this will have to be adjusted.

Sub sumsome()
Dim lastrow As Long, l As Long, h As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To lastrow
   If Range("D" & i) = "L1" Then
      l = l + Range("A" & i).Value
   Else
   If Range("D" & i) = "L3" Then
      l = l + Range("B" & i).Value
   End If
   End If
   If Range("D" & i) = "H1" Then
      h = h + Range("A" & i).Value
   End If
Next i


Range("G2") = l
Range("G3") = h


End Sub

 

[Kradores]

Thank you,

It isn't a formula, but it works
If instead of 2 cells I will have a table of 980 cells, will this work same way ?

 

[Marcelo Branco]

A possible solution using array formulas

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Urban​	Rural​	Subcode​		Criteria​	Result​		Code​	SubCode​	Location​
2​	18​	13​	L1​		L​	36​		L​	L1​	Urban​
3​	27​	14​	L3​		H​	72​		L​	L2​	Urban​
4​	72​	5​	H1​					L​	L3​	Rural​
5​	32​	57​						H​	H1​	Urban​
6​	4​	38​	L1​
7​

<tbody>
</tbody>

Code-SubCode-Location table in H2:J5 (gray area)

Criteria in column E

Array formula in F2 copied down
=SUM(IF(LEFT($C$2:$C$1000)=E2,IF(ISNUMBER(MATCH($C$2:$C$1000,$I$2:$I$5,0)),IF(INDEX($J$2:$J$5,N(IF({1},MATCH($C$2:$C$1000,$I$2:$I$5,0))))=$A$1:$B$1,$A$2:$B$1000))))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[LloydFinancials]

try it and let me know