SUMIF cells in Column B are blank

J

Josh Hendrickson

I am trying to figure out how to sum numbers in column A but only if the
cells in the corresponding row in column B are blank. Any ideas?

I thought this would work but it hasn't so far

=SUMIF(B:B, ISBLANK(B:B), A:A)
 
S

Sheeloo

=SUMPRODUCT(--(B1:B1000=""),A1:A1000)

In Excel 2003 you can not use A:A and have to specify the range...
 
C

Chip Pearson

Try the following formula:

=SUMPRODUCT((B1:B10="")*A1:A10)

Change range references to your needs.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 25 Oct 2008 11:50:01 -0700, Josh Hendrickson <Josh
 
J

Josh Hendrickson

Thank you all for your efforts however I can't seem to get my desired result.
I think I need to explain the much larger problem that I am trying to solve
to really figure this out.

I have two sheets within a workbook;

On sheet one I have three columns;

Column A has a list of dates, Column B has a list of dollar amounts and
Column C has random words

It looks somewhat like this

Column A Column B Column C
10/24/2008 $100 Dog
10/23/2008 $220 Cat
10/20/2008 $100
10/19/2008 $115 Dog
10/19/2008 $230
10/18/2008 $115 Mouse
10/16/2008 $300


On the second sheet I have a list of dates and I want to find out the total
revenue from column B on the first sheet between two dates, say 10/19/2008
and 10/23/2008 and that have words written in column C.

I then want to do the same thing with things that don't have words written
in Column C.

Any help would be greatly appreciated!
 
M

Max

In Sheet2,
Assume Startdate/Enddate inputs are in A2:B2 down
In B2
=SUMPRODUCT((Sheet1!$A$2:$A$10>=A2)*(Sheet1!$A$2:$A$10<=B2)*(Sheet1!$C$2:$C$10<>""),Sheet1!$B$2:$B$10)
Copy B2 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top