Excel SUMIFS Formula Sequential Date Criteria?

Question

=SUMIFS($I$2:$I$2221,$M$2:$M$2221,"VISA",$B$2:$B$2221,"June 1")

This is the formula I am using for a sales analysis worksheet, that takes the clutter from the POS report and breaks it down in a summary just as below. I want to find a way to make the date continue sequentially when I drag the corner of the first of June down to the other cells below. Any ideas?

Answer 1

To get the days to change, you can build a formula for that portion of the formula. Build it using something that will increment as you go down rows and you're in business.

So... instead of text, like "June 1", pick something like ROW() which will provide you a number based upon the row you are in. Let's say you start in row 3:

=ROW()-2

will yield "1" for a result. Combine it with concatenate or a similar option:

=CONCATENATE("June "&(ROW()-2))

which will yield "June 1" in row 3. Change out the June part for a "6" and a "/", then cobble a year onto the end of it (along with its own "/") for an input to the DATEVALUE function:

=DATEVALUE( CONCATENATE( "6/" & (ROW()-2) & "/2018" ))

yielding =DATEVALUE("6/1/2018") which will give a "real" date that Excel can nicely use as the third argument in your function.

As you copy it down rows, it will keep adding "1" and give you the rising dates you are asking for.

To get the "June 1" display rather than some unliked way for Excel to display the date, format your cells like so:

mmmm d

Using the four "m" characters will give you the "June" portion, the space will give you... the space... and the single "d" will give you the day without using the "01" style. It will be "1" or "22" but no leading "0". (Or use two "d" characters to get the two digit days with leading 0's from the 1st to the 9th, if you'd rather.)

Now, if you want to get fancier, you can test for whether a date exists or not. For example, you have to copy down 31 cells to cover months like July. But when it is February, the last three will show ugly looking ERROR! ERROR! ERROR! labels that will disturb your users. So wrap the above DATEVALUE() formula in an IFERROR() formula:

=IFERROR( DATEVALUE( CONCATENATE( "6/" & (ROW()-2) & "/2018" )), "" )

You can change out the "6/" portion for a reference to some cell that contains a month name, or some other variations, even bell and whistle kind of stuff like drop lists theuser chooses from, in order to change months. Looking up the month number from the name and a table hidden off somewhere using VLOOKUP() is fairly standard, or you could get funky and use the month name in a DATEVALUE() formula that changes it and a standard addition like "-2018" (any year would do, so long as it's past 1900) and wrapping it in MONTH(): (say your month name is in cell A1)

=MONTH( A1 & "-2018" )

which yields a "6". Wrap it in IFERROR() in case something wrong is entered and ou can have the opportunity to give your own error message:

=IFERROR( MONTH( A1 & "-2018" ), "Mmm... spelling?" )

or what-have-you.

It gets more complicated if you want to have more than one month shown, but not much. Just change the formula above that had the IFERROR() wrapper and used "" (blank) if the day number was too high for a month. Replace the "" portion with the DATEVALEU() portion but with "1" added to it to go to the next day which will start the next month and then...

Yes, and then... a problem crops up. It won't go to the second day. Maybe testing for the month and adding "1"... an easier idea pops up here... why not just take the previous date shown and add "1" to IT instead of continuing with an increasingly harder to make work formula?

But... you know... we could have done that in the 2nd cell... nice formula shown in the first cell, then this easier approach for ALL cells afterwards and month canges are taken care of for you... leap years too...

The formula to use in row 4 and all rows after it is:

=A3 + 1

(change "A" to be whatever column you are using, of course).

Now you have one problem left, if you only want a single month at a time and you do this. (Remember, the formula worked just fine for one month so you could just stick with it.) It doesn't have a way at the moment to notice a month change. So the February one would go 2-28, 3-1, 3-2, 3-3... and look a little... um, sutpid...

You could add a little more to solve that. Test the MONTH() of the result vs. the MONTH() of the cell above and yield "" if they do not match. That solves the first such cell, all months except February. Add a test using OR() to see if the above cell is blank and output a blank if it is. That will solve February:

=IF( OR( MONTH( A3 + 1 ) <> MONTH( A2 ), A2 = "" ), "", A3 + 1 )

Everything presented above, including the parts that built into a dead-ish end as you expanded your idea of how to use the work is fairly standard, chunk-along spreadsheet writing. I built the narrative rather than just giving the simple answer we ended up with to illustrate how thinking ahead AT THE START will often prevent nightmares later. Let others use the simple start and they will clamor for this and that and then you have a complicated mess that you take an hour to re-learn each time you have to make changes. Put a bit of thinking into how it might need "improved" in the future and you might lead yourself to a fairly simple, easy to understand and incrementally add functionality to kind of spreadsheet.

Notice also the solve each problem as it comes approach led to more and more complexity until all your intial problems were solved, somehow, and by reasonable approaches too, but left you with a hard to improve and extend piece of work though you did nothing dumb or wrong. The strategic thought you put into it at the start maybe would ahve given you the utterly simple "just add 1" approach at the beginning and no problems would really have needed solving. You'd've had a simple, extendable piece of work in less time and with less aggravation right form the start.

(Downside: you DO learn as you explore the complicated add on and jerry-rig path. That's meant to be funny, but it's true too. But it wasn't really your main goal, I'm betting.)

Answer 2

Excel is usually good at figuring out a sequence of numbers, but sometimes your data isn't always in a predictable sequence, so you have to provide some hints. Often, it is simply a simple matter of putting in "June 1" in one cell, formatting it the way you want, and then dragging the square handle in lower right corner of the selection rectangle. On the other hand, if some of your dates jump around or some other factors, Excel isn't always going to make the right assumption and may just print the same value over and over.

If you want to drag dates:

1. Enter June 1 in one cell
2. Enter June 2 in the cell below it. This gives Excel a hint as to the pattern you want to repeat which is necessary if all the other items in the
3. Excel will likely detect them as a date and reformat your cells as some date format other than "June 1" (like "1-Jun"). Highlight the cells, and change the number formatting to the custom format: [$-409]mmmm d;@ so that it will display the cells as you describe, without printing the year.
4. Once you have the cells formatted the way you want them, select both of the cells.
5. Hover over the lower right square dot in the selection rectangle. Your cursor should change to a "+" symbol.
6. Drag the "+" down with your primary mouse button, and the pattern will repeat. For more advanced options (like only repeating weekdays), drag with the secondary mouse button, and it will pop up a context menu with various choices.

This should repeat the sequence. This is also good for other patterns, like skipping every 3 days if you had used June 1 and June 4 as the pattern seeds.