VBA/formula: if there's a cell in column B = 1, column D = 1, select that row and copy value in column H

[JennV]

Hello there,

I'm hoping to get some help/insight on how I'm able to complete this task.

On Sheet 1:
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"] H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.3[/TD]
[/TR]
</tbody>[/TABLE]

On Sheet 2, for example, I want the value in column H in the row where column B=1 and column D=1 (in this case, "1.1").
Another example, I want the value in column H in the row where column B=1 and column D=2 (in this case, "4.3").

Thank you in advance!

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Maybe something like this

Sheet2

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

A​

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

B​

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

C​

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

1​

[/td][td]

Crit1​

[/td][td]

Crit2​

[/td][td]

Result​

[/td][/tr]

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

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1,1​

[/td][/tr]

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

3​

[/td][td]

1​

[/td][td]

2​

[/td][td]

4,3​

[/td][/tr]

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

4​

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

Criteria in columns A:B

Formula in C2 copied down
=SUMIFS(Sheet1!H:H,Sheet1!B:B,A2,Sheet1!D:D,B2)

Hope this helps

M.

 

[JennV]

Welcome to Mr Excel Forum

Maybe something like this

Sheet2

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

A​

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

B​

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

C​

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

1​

[/TD]
[TD]

Crit1​

[/TD]
[TD]

Crit2​

[/TD]
[TD]

Result​

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

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1,1​

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

3​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

4,3​

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

4​

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


Criteria in columns A:B

Formula in C2 copied down
=SUMIFS(Sheet1!H:H,Sheet1!B:B,A2,Sheet1!D:D,B2)

Hope this helps

M.

This worked! Thank you so, so much!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[JennV]

You are welcome. Thanks for the feedback.

M.

I'm sorry, I have a follow-up question if you don't mind. What if there's no value in column H (a blank cell) and I want that copied over to sheet 2?

 

[Marcelo Branco]

The formula returns 0 if H is blank.

M.

 

[JennV]

The formula returns 0 if H is blank.

M.

Is it possible to copy that blank?

 

[Marcelo Branco]

Is it possible to copy that blank?

What you mean by "copy that blank"? You want a blank result instead of zero, do you?

M.

 

[JennV]

What you mean by "copy that blank"? You want a blank result instead of zero, do you?

M.

Yes, sorry - I should've finished my sentence.

Using your SUMIFS formula, blanks cells in column H of sheet1 = 0 in sheet2. I am familiar with making that 0 look blank using conditional formatting (visually/presentation) but I don't want that cell to have the value 0 but to simply be blank as it is on sheet1 (no value). Is there a possibility of making this happen? I'm assuming another function needs to be used?

Thanks for being so patient with me. I'm on a project with very limited Excel knowledge so this forum is helping me learn alot!

 

[Marcelo Branco]

Try something like this

=IF(<formulahere>formulahere=0,"",formulahere<formulahere>)

M.</formulahere></formulahere>