Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF except for blank cells | Excel Discussion (Misc queries) | |||
SUMIF does not work with blank cells | Excel Discussion (Misc queries) | |||
Sumif Cells Are Not Blank | Excel Worksheet Functions | |||
Sumif function does not add up cells, even when criteria is blank | Excel Worksheet Functions | |||
Using SUMIF with non-blank cells | Excel Worksheet Functions |