SUMIF cells in Column B are blank

  • Thread starter Thread starter Josh Hendrickson
  • Start date Start date
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)
 
=SUMPRODUCT(--(B1:B1000=""),A1:A1000)

In Excel 2003 you can not use A:A and have to specify the range...
 
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
 
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!
 
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
 
Back
Top