SUMIF

[Guest]

I have two columns:
A B
205 Johnny Smith
120 Gerald Jones
284
179 Frank Bend



What function can I use... to say if there is a value in column B, I want
you to add the corresponding value of column A

Im making a template, so it references another sheet:


=SUMIF(Sheet2!B2:B10, COUNTA(Sheet2!B2:B10), Sheet2!A2:A10)


and it just isn't working.

 

[Dave Peterson]

=SUMIF(sheet2!B2:B10,"<>",sheet2!A2:A10)

I have two columns:
A B
205 Johnny Smith
120 Gerald Jones
284
179 Frank Bend

What function can I use... to say if there is a value in column B, I want
you to add the corresponding value of column A

Im making a template, so it references another sheet:

=SUMIF(Sheet2!B2:B10, COUNTA(Sheet2!B2:B10), Sheet2!A2:A10)

and it just isn't working.

 

[Guest]

Here's one interp on your underlying intent, and a way to dynamically pull
over only the source lines where col B (names) is not blank

Assuming source data as posted is in Sheet2, cols A and B, from row2 down

In Sheet1,

In A2:
=IF(Sheet2!B2="","",ROW())
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of
data in Sheet2. Hide away col A. Cols B and C will return only the lines from
Sheet2 where col B is not blank, all neatly bunched at the top.

 

[Guest]

You can this function:
=SUMPRODUCT(--(B1:B4<>""), A1:A4)

 

[Guest]

You can use

=SUMPRODUCT(--(B1:B4<>""), A1:A4)