# Solved SUMIF with 2 criteria but 1 of them may be a blank cell

May 21, 2013 at 22:19:25
Specs: Windows XP
 I'm struggling to get a formula working that does what I require. I have 2 columns of data that are used in the formula. Column G contains either a date (expressed as 1 for January, 2 for February, etc) or a blank cell. Column J contains a dollar value. I want to sum whatever is in column J only if there is a date in column G. If the cell in column G is empty I want Excel to do nothing. At the moment I have the following array:{=SUM(IF(MONTH(G2:G1000)=10,J2:J1000))}What do I do to make Excel not to sum the value in column J if the corresponding cell in column G is empty? I am using Excel 2003. Thank you!

See More: SUMIF with 2 criteria but 1 of them may be a blank cell

May 22, 2013 at 19:13:33
 Here's a =SUMPRODUCT() function that should work for you:=SUMPRODUCT(--(G2:G1000>=DATE(2013,1,1)),--(G2:G1000<=DATE(2013,1,31)),J2:J1000)This is just for the Month of JanuaryMIKEhttp://www.skeptic.com/

#1
May 22, 2013 at 03:49:58
 Please explain this statement:"Column G contains either a date (expressed as 1 for January, 2 for February, etc) or a blank cell."Do you actually have a date in the cells or just a number? I'm confused by "expressed as 1 for January, 2 for February, etc."Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
May 22, 2013 at 07:01:59
 Your formula worked for me.With the Dates (10/01/2013 thru 10/20/2013) in cell G2 thru G11and with the number 10 in cells J2 thru J11Your formula returns 100 as the SumDelete a Date and it returns 90Delete a Number and it returns 90The only two things I can think of:Make sure your Dates are really Dates, not TEXT that looks like Dates,You can do that by changing the Format from Date to Numberif you get a 5 digit number then it's probably a date, if it does notchange then it is probably TEXT.and Since this is an ARRAY formula, remember you have toenter it using the CTRL-SHIFT-ENTER sequence, not just ENTER.MIKEhttp://www.skeptic.com/

Report •

#3
May 22, 2013 at 07:36:12
 Just one other thing, make sure your Blank cells are really BLANK,a cell with a SPACE Character in it is NOT Blank.You should get an VALUE error message ifone of your Date cells looks blank but is not.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
May 22, 2013 at 17:54:19
 Hi Mike, thank you for your reply. The cells in the 'Date' column are formated as: 'Custom' 'mmm-yy'. People would enter the date by picking from a drop-down list (data validation), and you are right. It works fine. The problem starts when the user ignores to pick a date, therefore the cell is left blank. And for a reason I don't understand when the cell is blank my formula adds the amount to January. Always to January...

Report •

#5
May 22, 2013 at 18:57:02
 And for a reason I don't understand when the cell is blank my formula adds the amount to January. Always to January...That's because the MONTH() function ALWAYS needs a Date,When you have a blank cell (or 0 in a cell) and test it using MONTHit will return January or 1.MIKEhttp://www.skeptic.com/

Report •

#6
May 22, 2013 at 19:13:33
 Here's a =SUMPRODUCT() function that should work for you:=SUMPRODUCT(--(G2:G1000>=DATE(2013,1,1)),--(G2:G1000<=DATE(2013,1,31)),J2:J1000)This is just for the Month of JanuaryMIKEhttp://www.skeptic.com/

Report •

#7
May 22, 2013 at 19:55:46
 Mike, perhaps I did not explain this correctly. I am not interested in January only. I need every value entered in G2:G1000 to be added to the corresponding month. So if, for example My amount in J2 is \$1000 and the date picked by data validation in G2 is April then somewhere else in my worksheet will say: April: \$1000. If I change April to May, then April will say \$0 and May will say \$1000. So that works perfectly. my array for April is: {=SUM(IF(MONTH(G2:G1000)=4,J2:J1000))}and my array for May is: {=SUM(IF(MONTH(G2:G1000)=5,J2:J1000))}etc, etc, I have one for every month.My only problem is that when G2 is empty, then the \$1000 always go to January, which is not correct.

Report •

#8
May 23, 2013 at 03:25:54
 my array for April is: {=SUM(IF(MONTH(G2:G1000)=4,J2:J1000))}and my array for May is: {=SUM(IF(MONTH(G2:G1000)=5,J2:J1000))}So simply change the Dates in the formula:April:=SUMPRODUCT(--(G2:G1000>=DATE(2013,4,1)),--(G2:G1000<=DATE(2013,4,30)),J2:J1000)May:=SUMPRODUCT(--(G2:G1000>=DATE(2013,5,1)),--(G2:G1000<=DATE(2013,5,31)),J2:J1000)June:=SUMPRODUCT(--(G2:G1000>=DATE(2013,6,1)),--(G2:G1000<=DATE(2013,6,30)),J2:J1000)MIKEhttp://www.skeptic.com/

Report •

#9
May 23, 2013 at 11:30:46
 You might want to consider the YEAR function instead of hard coding 2013 into your formula. If you'll be using this spreadsheet next year and later, you'll have to update the formulas to reflect the new year(s).Maybe something like this untested version:=SUMPRODUCT(--(G2:G1000>=DATE(YEAR(G2),4,1)),--(G2:G1000<=DATE(YEAR(G2),4,30)),J2:J1000)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#10
May 23, 2013 at 16:56:23
 Hi Mike, thank you for that. Both your suggestions work just as well as mine. However, they present the same problemm, when the corresponding cell in G column is empty Excel adds the \$ to January.

Report •

#11
May 23, 2013 at 17:11:39
 However, they present the same problem, when the corresponding cell in G column is empty Excel adds the \$ to January.It does not do that for me.Please read this How To and post a small sample of your Data:http://www.computing.net/howtos/sho...Also, please Copy/Paste the formulas you are using with the dataand we'll try to find out what is going on.MIKEhttp://www.skeptic.com/

Report •

#12
May 23, 2013 at 20:30:41
 For what it's worth, Mike's formulas do not add anything to January when the drop down choice is blank.I even included a couple of blanks in the list of choices for my drops downs and choosing them doesn't add anything to anything.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#13
May 23, 2013 at 20:45:15
 Just so you know, this is how I set up my spreadsheetto test out the formulas:``` G H I J 1) Jan-13 \$1.00 2) Jan-13 \$1.00 3) Jan-13 \$1.00 4) Jan-13 \$1.00 5) Jan-13 \$1.00 6) \$1.00 7) \$1.00 8) \$1.00 9) Feb-13 \$1.00 10) Jan-13 \$1.00 11) Mar-13 \$1.00 12) Apr-13 \$1.00 13) May-13 \$1.00 14) Jun-13 \$1.00 15) \$1.00 ```With my data as shown, the formula: =SUMPRODUCT(--(G2:G1000>=DATE(2013,1,1)),--(G2:G1000<=DATE(2013,1,31)),J2:J1000returns \$6.00, representing Rows 1,2,3,4,5,10Rows 6,7,8,15, which have NO dates, but do have dollar amounts do not get added to January.In column G are full dates:01/01/2013, 02/01/2013, 03/01/2013, 04/01/2013, 05/01/2013, 06/01/2013Which are then Formatted using your "mmm-yy" Custom Format.How does your data differ from the above?What else is going on? Any other formula or macros?MIKEhttp://www.skeptic.com/

Report •

#14
May 25, 2013 at 19:40:44
 Mike and DerbyDad03, I don't understand. I swear every time my G column is blank my amount in J adds to January.I am writing from home today and somehow the link to "Click Here Before Posting Data..." does not work. Any chance I can email the worksheet somewhere? Thank you!

Report •

#15
May 26, 2013 at 07:21:58
 I've sent you an email address via PM. It is a temporary email address, so don't share it with any one because it won't work once this question has been resolved.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#16
May 26, 2013 at 18:54:13
 DebyDad03, you said: <>.Please don't forget I am a novice here. What is PM? I checked my email and did not find any. I also tried to reply to the system generated emails and sent the spreadsheet that way but it bounced...

Report •

#17
May 27, 2013 at 02:10:07

Report •

#18
May 27, 2013 at 20:09:42
 I've sent the spreadsheet earlier today. Thanks!

Report •

#19
May 28, 2013 at 13:15:26
 Your email landed in my junk folder so I did not see it. I will try to find time to look at it tonight.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#20
May 28, 2013 at 17:25:44