SUMIF cells in Column B are blank
 

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)

SUMIF cells in Column B are blank
 

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

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

"Josh Hendrickson" wrote:

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)

SUMIF cells in Column B are blank
 

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
wrote:

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)

SUMIF cells in Column B are blank
 

In say, C1: =SUMIF(B:B,"<",A:A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Josh Hendrickson" wrote:
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)

SUMIF cells in Column B are blank
 

Errata, should be:
In C1: =SUM(A:A)-SUMIF(B:B,"<",A:A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

SUMIF cells in Column B are blank
 

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!







"Josh Hendrickson" wrote:

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)

SUMIF cells in Column B are blank
 

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$1 0)
Copy B2 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Josh Hendrickson" wrote:
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.

SUMIF cells in Column B are blank
 

Hi Max,

Actually it should be

=SUMIF(B:B,"",A:A)

--
Thanks,
Shane Devenshire


"Max" wrote:

In say, C1: =SUMIF(B:B,"<",A:A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Josh Hendrickson" wrote:
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)

SUMIF cells in Column B are blank
 

Actually it should be
=SUMIF(B:B,"",A:A)

Agree. Excellent catch there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

SUMIF cells in Column B are blank
 

Typo error in earlier description, sorry
The formula is to be in C2 (not B2), then copied down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---