馃攳
Excel Magic Trick 1405: Monthly Totals Report: Sales from Daily Records, Costs from Monthly Records - YouTube
Channel: unknown
[0]
welcome to Excel magic number 1405 hey
[4]
if you want to download this excel
[6]
workbook so you can follow along click
[9]
on the link below the video we got to
[11]
see how to use some ifs
[13]
to calculate monthly revenue from daily
[15]
sales and then monthly ad costs from
[18]
monthly amount now here's our two tables
[22]
this one has the sales each transaction
[24]
is for a particular day so we need to
[27]
add all of the sales and get a total
[30]
over here for each one of the months and
[33]
then we need to do something similar but
[35]
from this table which lists the ad costs
[38]
for each particular product for the
[40]
month so in essence there's going to be
[42]
three monthly amounts that we need to
[45]
add up over here in both cases we can
[48]
use some it's now if we're only given
[52]
month and here well that will work as
[55]
our condition or criteria for Sumit's to
[57]
add the cost but what are we going to do
[59]
for revenue well we're given the month
[61]
end all we need to do is somehow
[63]
generate inside our formula the
[66]
beginning of each month and that will
[69]
give us our upper and lower limit now
[71]
I'm going to start this without doing
[73]
some ifs I'm just going to look at the
[75]
end of the month function now end of the
[78]
month takes a particular star to eat and
[81]
notice it's already the end of the month
[83]
comma now for months we can put a 1 to
[87]
go forward and get the end of next month
[89]
0 for the current month which is give us
[92]
the same day and minus ones go back to
[95]
the last day of the previous month so
[97]
I'm going to use minus 1 close
[99]
parentheses control enter when that has
[102]
currency to see it as a date control one
[105]
go over to number date and enter and
[109]
that gives us of course the end of last
[111]
month so what do we do f2 we add one
[115]
control enter and there is the beginning
[118]
of the month now we're going to use this
[120]
date and this data we need to say hey
[123]
all the records over here I need you to
[125]
look through the date column pick out
[127]
each sale when it's greater than or
[129]
equal to the lower date and less than or
[132]
equal to
[133]
the upper gate I'm going to hit f2 with
[136]
our cursor after the equal sign I'm
[139]
going to put some ifs
[141]
now the sum range now I'm using Excel
[144]
tables here so I've already converted
[145]
this to an Excel table which makes it
[148]
really convenient to highlight a whole
[150]
column I'm going to click near the top
[152]
of the field name and when I see that
[154]
downward-pointing black arrow I can
[156]
click and it puts the entire column into
[159]
our formula now that's table formula
[162]
nomenclature table name and then in
[165]
square brackets it lists the field name
[168]
comma now our criteria range we need to
[171]
look at dates I'm use my black downward
[173]
pointing arrow click and there it is
[175]
table name field name in square brackets
[178]
comma now that right there is the lower
[181]
limit and I need to say any date over
[184]
here that's greater than or equal to so
[187]
double quotes greater than first symbol
[190]
equal sign second symbol and double
[194]
quotes those comparative operators and
[196]
so nicks and Countess always have to be
[197]
in double quotes then we use the
[199]
ampersand that's the join symbol to join
[202]
it to that end of the month now notice
[205]
we have a plus operation here and a join
[208]
operation here the order of how
[211]
operations are calculated in Excel all
[215]
math operations happen before any join
[218]
operation so we don't need any special
[220]
parentheses that plus will calculate
[223]
before the ampersand
[225]
now that's criteria 1 so I come to be in
[228]
comma we need to list as criteria range
[231]
to the date column a second time so with
[234]
my downward pointing arrow I click there
[237]
it is comma and now I in double quotes
[240]
less than or equal to n double quote and
[243]
join it to the upper limit and that will
[247]
do it closed parentheses control enter
[251]
control one currency click OK and now I
[257]
can copy this down come to the last cell
[260]
f2 I'm looking to see if all my cell
[263]
references are correct looking good
[266]
now add costs since we already have the
[269]
end of the month we just say hey look
[271]
through this date column using that as
[274]
the condition and then add the
[275]
appropriate cost so I'm going to use
[277]
equal sum if it's some range that's
[281]
going to be add costs comma criteria
[284]
range that's going to be end of the
[287]
month comma and then as a single end of
[291]
the month date that's the conditioner
[293]
criteria close parentheses control enter
[296]
and copy it down now I'm going to come
[300]
down to the bottom and use the keyboard
[302]
alt equals that puts in the sum function
[305]
and I'm checking to see if it gets
[307]
correctly and it did ctrl enter
[310]
and now copy to the side and there we
[313]
have revenue and cost for each month
[316]
using some ifs and end of the month and
[320]
just a straight sum it's alright we'll
[323]
see you next video
Most Recent Videos:
You can go back to the homepage right here: Homepage