SUMIF across multiple columns and one row

[ryxk1]

I need to sum where criteria is matched in 2 columns and 1 row:


[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]O[/TD]
[TD="width: 64"]P[/TD]
[TD="width: 64"]2016[/TD]
[TD="width: 64"]2017[/TD]
[TD="width: 64"]2018[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="bgcolor: #E7E6E6"]*[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]*[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>


Using the example above, I need to sum if column O matches ‘A’, column P matches ‘*’ and the first row matches ‘2017’. So the answer I should get should be 4+3=7.
<strike></strike>

 

[Joyner]

Hello and welcome to the board, assuming all starts in cell A1:

=SUMIFS(INDEX(C2:E4,,MATCH(2017,C1:E1,0)),A2:A4,"A",B2:B4,"*")


Replace "2017" in the formula for the cell reference where that criteria is

 

[ryxk1]

Thank you for the reply. I tried to use your formula but it says #N/A

 

[ryxk1]

Hello and welcome to the board, assuming all starts in cell A1:

=SUMIFS(INDEX(C2:E4,,MATCH(2017,C1:E1,0)),A2:A4,"A",B2:B4,"*")


Replace "2017" in the formula for the cell reference where that criteria is

Thank you for the reply. I tried to use your formula but it says #N/A​

 

[Joyner]

Worked for me, try for the last criteria, "~*"

since * is a wildcard you may need it, but it worked for me

or you may also need to put 2017 in quotes "2017".

 

[Special-K99]

Works fine for me too.

 

[ryxk1]

Worked for me, try for the last criteria, "~*"

since * is a wildcard you may need it, but it worked for me

or you may also need to put 2017 in quotes "2017".

Think I entered the formula wrong, works a charm now. Thank you!

 

[Joyner]

You are welcome

 

[Marcelo Branco]

I think this formula is not robust. I suggest SUMPRODUCT

Suppose a new row like row 5 below

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

O​

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

P​

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

Q​

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

R​

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

S​

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

T​

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

U​

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

V​

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

W​

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

X​

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

Y​

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

Z​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2016​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD][/TD]
[TD]

Criteria1​

[/TD]
[TD]

Criteria2​

[/TD]
[TD]

Criteria3​

[/TD]
[TD]

SUMPRODUCT​

[/TD]
[TD]

SUMIFS w/o ~​

[/TD]
[TD]

SUMIFS with ~​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

*​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

*​

[/TD]
[TD]

2017​

[/TD]
[TD]

7​

[/TD]
[TD]

9​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

X​

[/TD]
[TD]

2017​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

A​

[/TD]
[TD]

*​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

A​

[/TD]
[TD]

X​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in X2 copied down
=SUMPRODUCT(($O$2:$O$5=U2)*($P$2:$P$5=V2)*($Q$1:$S$1=W2),$Q$2:$S$5)

Formula in Y2 copied down (note the wrong result in Y2)
=SUMIFS(INDEX($Q$2:$S$5,0,MATCH(W2,$Q$1:$S$1,0)),$O$2:$O$5,U2,$P$2:$P$5,V2)

Formula in Z2 copied down (note the wrong result in Z3)
=SUMIFS(INDEX($Q$2:$S$5,0,MATCH(W2,$Q$1:$S$1,0)),$O$2:$O$5,U2,$P$2:$P$5,"~"&V2)

M.

 

[Joyner]

I think this formula is not robust. I suggest SUMPRODUCT

M.

I agree, but if "*" is all that can be in the column I think it should be okay, or if possible, I would not use the wildcard character "*" and replace it with something else.