#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 58
Default SUMIF

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default SUMIF

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





Brandon wrote:

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default SUMIF

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,SM ALL($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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brandon" wrote:
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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default SUMIF

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


"Brandon" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default SUMIF

You can use

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


"Brandon" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" Harry Seymour Excel Worksheet Functions 9 June 12th 07 11:47 PM
Embedding a Sumif in a sumif C.Pflugrath Excel Worksheet Functions 5 August 31st 05 08:31 PM
Sumif help... Jambruins Excel Discussion (Misc queries) 1 June 22nd 05 04:00 PM
nested sumif or sumif with two criteria dshigley Excel Worksheet Functions 5 April 5th 05 04:34 AM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Excel Worksheet Functions 2 January 12th 05 12:01 AM


All times are GMT +1. The time now is 04:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"