Returning values

[JasmineL]

I'm playing with a formula and must have a brain fart. Here's what I want...the formula looks in column B and if there something in cell B1 and B2, the formula returns the sum of the corresponding values in cells A1 and A2. I know this is easy, but I can't remember how to do it. Please help!

 

[VoG]

Try

=IF(COUNTA(B1:B2)=2,A1+A2,"")

 

[mgirvin]

Try this:

=IF(AND(B1<>"",B2<>""),SUM(A1:A2),"")

Or try this shorter one, but you must enter it with the keystrokes Ctrl + Shift + Enter:

=IF(AND(B1:B2<>""),SUM(A1:A2),"")

These will return a blank in there is not data in both B1 and B2.

 

[JasmineL]

Thank you for the suggestions. My issue is this...what if I'm searching in a range of cells, B4:B70. B4 and B20 aren't empty, so I want the sum of the values in A4 and A20. Does this clarify a bit?

 

[VoG]

So what if 3 cells, or 15 cells, aren't empty. Do you want the sum of the corresponding cells in column A?

 

[JasmineL]

Yes Sir! If the values in 50 cells in column B in the specified range aren't empty, then I want the sum of their corresponding values in column A.

 

[VoG]

Please don't call me Sir - it makes me nervous

Try

=SUMIF(B4:B70,"<>",A4:A70)

 

[JasmineL]

Oh my goodness, I was so close...thank you! nonSir Is there an AND I can add to disregard cells which do not have visual entries, but might have hidden spaces?

 

[VoG]

There's a better way to do this but being a formula moron I cannot get the right syntax. Try

=SUMIF(B4:B70,"<>",A4:A70)-SUMIF(B4:B70," *",A4:A70)

 

[JasmineL]

Thank you...I was testing this and so far it seems to work:

=SUMIF(B4:B70,"> *",A4:A70)