How to find and SUM Multiple Column Data by two criteria?

[knaabis]

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th></th><th>w</th><th>e</th><th>w</th><th>k</th><th>w</th></tr></thead><tbody>
<tr><td>A</td><td>10</td><td>5</td><td>20</td><td>4</td><td>8</td></tr>
<tr><td>B</td><td>9</td><td>4</td><td>5</td><td>4</td><td>2</td></tr>
<tr><td>C</td><td>11</td><td>3</td><td>18</td><td>9</td><td>7</td></tr>
<tr><td>D</td><td>8</td><td>2</td><td>10</td><td>3</td><td>4</td></tr>
<tr><td>E</td><td>25</td><td>1</td><td>3</td><td>3</td><td>5</td></tr>
<tr><td>F</td><td>3</td><td>1</td><td>3</td><td>3</td><td>5</td></tr>
</tbody></table>

I need SUM by two parameters - in Row side (for example) parameter A and for Column side parameter W.
Result will be 38.

 

[James006]

Hello,

With your table located in range A1:F7, you can have following Array Formula:

=SUM(IF(B1:F1="w",OFFSET((B1:F1),MATCH("A",A2:A7,0),0),0))

Hope this will help

 

[Fluff]

Another option

Book1
	H	I	J	K	L	M	N	O	P
3		w	e	w	k	w			w
4	A	10	5	20	4	8		A	38
5	B	9	4	5	4	2
6	C	11	3	18	9	7
7	D	8	2	10	3	4
8	E	25	1	3	3	5
9	F	3	1	3	3	5
Sheet1

Cell Formulas
Range	Formula
P4	=SUMPRODUCT((H4:H9=O4)*(I3:M3=P3)*(I4:M9))

 

[Marcelo Branco]

Or


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

w​

[/td][td]

e​

[/td][td]

w​

[/td][td]

k​

[/td][td]

w​

[/td][td][/td][td]

Crit1​

[/td][td]

Crit2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

10​

[/td][td]

5​

[/td][td]

20​

[/td][td]

4​

[/td][td]

8​

[/td][td][/td][td]

A​

[/td][td]

w​

[/td][td]

38​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

B​

[/td][td]

9​

[/td][td]

4​

[/td][td]

5​

[/td][td]

4​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

C​

[/td][td]

11​

[/td][td]

3​

[/td][td]

18​

[/td][td]

9​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

D​

[/td][td]

8​

[/td][td]

2​

[/td][td]

10​

[/td][td]

3​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

E​

[/td][td]

25​

[/td][td]

1​

[/td][td]

3​

[/td][td]

3​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

F​

[/td][td]

3​

[/td][td]

1​

[/td][td]

3​

[/td][td]

3​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in J2
=SUMIF(B$1:F$1,I2,INDEX(B$2:F$7,MATCH(H2,A$2:A$7,0),0))

M.

 

[knaabis]

Hello,

With your table located in range A1:F7, you can have following Array Formula:

=SUM(IF(B1:F1="w",OFFSET((B1:F1),MATCH("A",A2:A7,0),0),0))

Hope this will help

Great!
Thanks!

 

[James006]

You are welcome