SUM range, but if adjecent cell is not blank, sum that value

[jaxazam]

Hey guys, I'm stuck:

I'm trying to do a personalized budget worksheet, but am running into an issue.

Column A has the item, column B lists it as a necessity or a wish. Column C represents the budgeted (expected) price of my item, and after I buy it, column D represents the true value of final sale. I want to have a cell that displays the result of the total cost of all my necessities. C

Currently, I have =SUMIF(B:B,"=Need",C:C), which would yield $83.99 which would sum all the expected. What I want is if the adjacent cell has a value, I want to include that price INSTEAD OF column C (so the result will be $74.99 instead). Any thoughts on how to do this? Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item (A)[/TD]
[TD]Want or Need (B)[/TD]
[TD]Expected Price (C)[/TD]
[TD]Actual Price (D)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Laptop[/TD]
[TD]Need[/TD]
[TD]$700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Headphones[/TD]
[TD]Want[/TD]
[TD]$75[/TD]
[TD]$65[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Groceries[/TD]
[TD]Need[/TD]
[TD]$89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Netflix[/TD]
[TD]Want[/TD]
[TD]$8.99[/TD]
[TD]$9.99[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel

Try this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

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

1​

[/td][td]

Item (A)​

[/td][td]

Want or Need (B)​

[/td][td]

Expected Price (C)​

[/td][td]

Actual Price (D)​

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

Criteria​

[/td][td]

Total​

[/td][/tr]


[tr][td]

2​

[/td][td]

Laptop​

[/td][td]

Need​

[/td][td]

700​

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

Want​

[/td][td]

74,99​

[/td][/tr]


[tr][td]

3​

[/td][td]

Headphones​

[/td][td]

Want​

[/td][td]

75​

[/td][td]

65​

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


[tr][td]

4​

[/td][td]

Groceries​

[/td][td]

Need​

[/td][td]

89​

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


[tr][td]

5​

[/td][td]

Netflix​

[/td][td]

Want​

[/td][td]

8,99​

[/td][td]

9,99​

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

Formula in G2
=SUMPRODUCT(--($B$2:$B$5=$F2),$C$2:$C$5*($D$2:$D$5="")+$D$2:$D$5)

M.

 

[jaxazam]

Perfect! Thanks you so much!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.