Add up values of range of cells, if the corresponding column is emply

[brianparrish]

I have a range of cells in column C (C1:C100) that has dates in them. In column B (B1:B100), there are dollar amounts. Some of the cells in column C don't have dates (yet). I'd like to be able to add up the dollar amounts in column B that don't have corresponding dates is column B.

Example

B C
1 100 01/01/19
2 299 02/09/19
3 150
4 200
5 300 03/03/19

150+300=450, where the function ignores B1, B2, & B5.

Thanks in advance!

 

[CalcSux78]

Try SUMPRODUCT:

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]299[/TD]
[TD="align: right"]2/9/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]3/3/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]350[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]B6[/TH]
[TD="align: left"]=SUMPRODUCT(--(C1:C5=0),B1:B5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[brianparrish]

Try SUMPRODUCT:

Excel 2010

	A	B	C

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]299[/TD]
[TD="align: right"]2/9/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]3/3/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]350[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=SUMPRODUCT(--(C1:C5=0),B1:B5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Awesome! That works great. Thanks again!!

 

[DanteAmor]

You can also use

=SUMAR.SI(C1:C100,"",B1:B100)