Click here for important information about Computing.net.

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!

✔ 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

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.

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 J11Your formula returns 100 as the Sum

Delete a Date and it returns 90

Delete 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 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

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

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...

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

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

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.

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

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.

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.

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

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.

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.00With 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,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

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!

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.

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...

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.

I've sent the spreadsheet earlier today. Thanks!

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.

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.

Hello again, I have implemented all your changes and it works magnificently. I can't thank you enough!

Regards,

SilviaB

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History