Welcome to Excel Magic Trick number 1,405.
Hey, if you want to download this Excel
workbook so you can follow along, click on the link
below the video.
Hey, we got to see how to use sumif
to calculate monthly revenue from daily sales
and then monthly ad costs from monthly amounts.
Now, here's our two tables.
This one has the sales.
Each transaction is for a particular day.
So we need to add all of the sales and get a total over here
for each one of the months.
And then we need to do something similar,
but from this table, which lists the ad
costs for each particular product for the month.
So in essence, there's going to be three monthly amounts
that we need to add up over here.
In both cases, we can use sumifs.
Now, if we're only given month end here,
well, that will work as our condition or criteria
for sumifs to add to costs.
But what are we going to do for revenue?
Well, we're given the month end.
All we need to do is somehow generate inside our formula
the beginning of each month, and that will give us
our upper and lower limit.
Now, I'm going to start this without doing sumifs,
and I'm just going to look at the end of the month function.
Now, end of the month takes a particular start date.
And notice, it's already the end of the month, comma.
Now, for months we can put a 1 to go forward and get
the end of next month, 0 for the current month, which
would give us the same day, and minus 1
to go back to the last day of the previous month.
So I'm going to use minus 1, closed parentheses,
Control-Enter.
Oh, and that has currency.
To see it as a date, Control-1, go over to number, date,
and Enter.
And that gives us, of course, the end to last month.
So what do we do?
F2-- we add one.
Control-Enter, and there is the beginning of the month.
Now, we're going to use this date and this date,
and we need to say, hey, all of the records over here,
I need you to look through the date column,
pick out each sale, when it's greater
than or equal to the lower date and less than
or equal to the upper date.
I'm going to hit F2.
With our cursor, after the equal sign, I'm going to put sumifs.
Now, the sum range--
now, I'm using Excel tables here,
so I've already converted this to an Excel table, which
makes it really convenient to highlight a whole column.
I'm going to click near the top of the field name,
and when I see that downward-pointing, black arrow,
I can click, and it puts the entire column into our formula.
Now, that's table formula nomenclature-- table
name, and then in square brackets,
it lists the field name, copmma.
Now, our criteria range-- we need to look at date.
So I'm going to use my black, downward-pointing arrow.
Click, and there it is-- table name, field name
in square brackets, comma.
Now, that right there is the lower limit.
And I need to say, any date over here that's
greater than or equal to--
so double quotes, greater than, first symbol, equal sign,
second symbol, end double quotes--
those comparative operators in sumifs and countifs
always have to be in double quotes.
Then we use the ampersand-- that's
the join symbol-- to join it to that end of the month.
Now, notice we have a plus operation here
and a joint operation here.
The order of how operations are calculated in Excel, all math
operations happen before any joint operations,
so we don't need any special parentheses.
That plus we'll calculate before the ampersand.
Now, that's criteria1, so I come to the end, comma.
We need to list as criteria range to the date
column a second time.
So with my downward-pointing arrow, I click.
There it is, comma.
And now I-- in double quotes-- less than
or equal to end double quote, and join it to the upper limit.
And that will do it.
Close parentheses, Control-Enter, Control-1,
currency.
Click OK.
And now I can copy this down.
Come to the last cell, F2.
I'm looking to see if all of my cell references are correct.
Looking good.
Now, add costs-- since we already
have the end of the month, we just
say, hey, look through this date column,
using that as the condition, and then add the appropriate cost.
So I'm going to use equals sumifs the sum range.
That's going to be add costs, comma.
Criteria range-- that's going to be end of the month, comma.
And then as a single end of the month date,
that's the condition or criteria.
Close parentheses, Control-Enter,
and copy it down.
Now, I'm going to come down to the bottom and use the keyboard
Alt+Equals.
That puts in the sum function.
And I'm checking to see if it guessed correctly, and it did.
Control-Enter, and now copy to the side.
And there we have revenue and cost
for each month using sumifs and end of the month, and just
a straight sumifs.
All right.
We'll see you next video.