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

Report •

✔ Best Answer
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 January

MIKE

http://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 G11
and with the number 10 in cells J2 thru J11

Your formula returns 100 as the Sum

Delete a Date and it returns 90
Delete a Number and it returns 90

The 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 Number
if you get a 5 digit number then it's probably a date, if it does not
change then it is probably TEXT.

and

Since this is an ARRAY formula, remember you have to
enter it using the CTRL-SHIFT-ENTER sequence, not just ENTER.

MIKE

http://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 if
one of your Date cells looks blank but is not.

MIKE

http://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 MONTH
it will return January or 1.

MIKE

http://www.skeptic.com/


Report •

#6
May 22, 2013 at 19:13:33
✔ Best Answer
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 January

MIKE

http://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)

MIKE

http://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 data
and we'll try to find out what is going on.

MIKE

http://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 spreadsheet
to 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:J1000

returns $6.00, representing Rows 1,2,3,4,5,10

Rows 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/2013
Which 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?

MIKE

http://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: <<I've sent you an email address via PM>>.

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
Every member of this forum has the ability to send Private Messages to other members.

Once you have logged in, click on the My Home link at top of this page. You should see a number of links related to your membership. One of them should be the Private Message Center.

There may also be a section in the upper right called Your Tracked Posts/Messages. Under that you may see a link to you Private Messages.

Please do not use PM's to ask help related questions since the Information will not be shared with others or stored in the archives for others to learn from.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
OK, so tell us why you aren't using the formulas that Mike suggested? Your spreadsheet still has all of the {=SUM(IF(MONTH(G2:G1000)=10,J2:J1000))} array formulas in J1010:K1021.

As soon as I replaced the formulas with Mike's SUMPRODUCT versions, the January issue went away.

OK, so now I'm going to make your life a lot easier. The copy of the spreadsheet I've emailed back to you has these changes, but I'll described them here so that you will know how they work.

1 - In G1010 paste this: =INDEX(Month,ROW()-1009)
2 - Drag this down to G1021
>> This should fill your dates through June 1, 2014 by "copying" them from your Named Range, "Month"
4 - Select G1010:G1021 and format them as Custom mmmm
>> This should give you just the Month names; you might have to left justify them.

Paste this into J1010:

=SUMPRODUCT(--($G$2:$G$1000>=DATE(YEAR($G1010),MONTH($G1010),DAY($G1010))),
--($G$2:$G$1000<=DATE(YEAR($G1010),MONTH($G1010),DAY($G1010))),J$2:J$1000)

Now drag this over and down to fill in J1010:K1021

1 - You'll note that the use of the dollar signs lock the ranges, allowing you to drag the single formula over and down.

2 - By using real dates in Column G, the DATE function determines whether the Months match without having to use any hard coded values in the formula.

3 - By using the INDEX function on your Named Range, you can simply change the values in the Named Raange and both the your drop downs and G1010:G1021 will be updated with the new dates.

One additional tip: Since MONTH is a built-in Excel function, it can be confusing to use the same term for a Named Range. You might want to change that to myMonths or something like that just so you don't confuse yourself or others later on.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
May 29, 2013 at 04:12:21
Hello again, I have implemented all your changes and it works magnificently.

I can't thank you enough!

Regards,
SilviaB


Report •

Ask Question