COUNTIFS, a PivotTable or Power Query with import text files.
The next three videos we'll see how
to create this crosstab report where
we count work hours less than zero by name, description
for each month and from the disposition column
divide or one of three other conditions or criteria.
Now COUNTIFS will be the most difficult to create,
but Excel worksheet formulas are the only feature
that will instantly update when formula inputs change.
PivotTables are by far the easiest to create.
And although they don't update instantly,
a simple refresh will update our report.
And Power Query is what we want to use
when we have external data and we
expect to get more data later.
Now one problem with formulas that we
don't have with PivotTables is we
have to somehow create all of the conditions and criteria.
Now you can go and copy a column,
copy it off to the side data, remove duplicates
to create something like this or you
can use some of the Office 365 dynamic array formulas
like UNIQUE, but we're going to concentrate
on the formula that goes on the interior
of this crosstab report.
Now we can use the amazing function
COUNTIFS that will easily count with six
conditions or criteria.
The trick is from our source table,
we're going to have to match up the column which
goes in criteria range with the criteria.
Now for this report here we have name, description, disposition.
But the date is actually going to give us trouble
because when the formula copies to the side
and down, all the conditions are easy to go and get with cell
references except for the date.
Because for that date, when we copy it to the side,
it needs to be locked until it gets to this section.
So that's tricky.
And we'll see three different ways to deal with it.
All right.
So let's start this formula.
We'll start with the name condition first,
criteria range 1.
Because this is an Excel table, I hover my cursor
and when I see the black downward pointing arrow,
I click.
That'll select the whole column.
We see the table name and column name in square brackets, comma.
Criteria 1, we're matching the condition or criteria
with our column.
Now when we copy this formula down,
we want to see the new condition in each row.
But when I copy it to the side, I need it locked.
So we hit the F4 key one, two, three different times.
With a dollar sign in front of the I,
it means when we copy it to the side it will not move.
But when we copy it down the nine
will move to 10, 11 and so on, comma.
Criteria range 2.
We're going to go for our second column that's description,
click, comma.
Criteria 2, selecting J8.
Now we hit the F4 key one, two times.
We're locking the row so as we copy down it's locked,
but not the J. So when we copy to the side
it will move to k, comma.
Criteria range 3.
Disposition, comma.
It's in J6, and we hit the F4 key
to lock it in all directions, comma.
The next column we're going to select work hours,
and now we type a comma.
We need less than zero, so we hard
code this one in, double quotes, then the comparative operator
less than 0 in double quotes, comma.
And the criteria range 5 and 6 are going
to both be the date column.
And what we need to do is, since we have the first of the month,
I need to compare the date column
and ask the question are you greater than
or equal to the first of the month, which is the lower limit
and are you less than or equal to the end of the month?
So in criteria 5 we'll select date, comma, and the lower
limit means we need double quotes
in the comparative operator are you greater than
or equal to, end double quotes.
We have to join it to and, for the time
being in J7, that's the 1st of December, we'll hit the F4 key.
That'll work for now.
That's the lower limit.
Now we type comma, and the final range will be date again,
comma, and here we need to create the upper limit.
So we ask, double quotes, are you less than
or equal to, end double quotes, and join it to--
well, I can't just use the first of the month
because I need the end of the month.
I'm going to hit the F4 key and we'll convert December 1
to the end of the month by using the end of the month function.
Start date, comma, and we want to jump
0 months, that means we want the end of December,
close parentheses.
Now that formula right there will almost work,
close parentheses.
Now when I Control Enter, we can't
use the crosshair or angry rabbit
to copy because the column references will move
as relative cell references.
But we can highlight the entire range
and, with the active cell at the top, I hit F2,
and I'm going to use Control Enter.
That is one way to copy.
We could have used Control C, Control V and, in fact,
let me show you another way.
Control Z, we could here do control R
to copy it to the right and then Control D to copy it down.
Now I'm going to go to the last cell
and we're going to have a date problem.
But what I want to make sure, when I F2,
is I just want to make sure that I have all the right
column's-- name, description, disposition, and so on.
But already, from this last cell,
I can see for February that's the wrong date.
So here's the cheap and easy way to do this.
I highlight this section, F2, and just use my Move cursor.
So click and drag and then Control Enter.
Same here, F2, move.
So that's the cheap and easy way.
Control Enter, and our formula works.
If I come up here and change this to transfer,
everything's working.
Now if we come up to the top F2, the problem
is when we copy the formula it's totally locked on December.
But what I want is copy it to the side, it's locked,
but when I get to this column right here I want January
and then when I copy it further I want February.
Now we're going to see two tricks.
The first trick is instead of using the actual cell
we'll use the function EDATE.
And EDATE can jump forward a certain amount of months.
So in my formula if I had EDATE 0 0 0, it wouldn't jump.
But if all of a sudden EDATE had 1 1 1,
it would jump from December to January, and over here 2 2 2.
So if we use EDATE, that means we
need a formula number incrementor that
gives us zeros ones and twos.
So down below, I'm going to see if I can create that number
incrementor, we'll use the columns function
and I'm eventually going to be in cell J9,
so I type $J9, colon, J9, close parentheses.
That's an expandable range where the first J is locked
but the second one is not.
Since I'm copying across the columns,
Control Enter this increments the numbers 1 to 9.
If I copy it down, that's not what I want
but that's the interior part of what we want.
Now F2, I want to subtract 1 because we're
going to need to start from 0, Control
Enter, we get 0 to 8, F2.
If I take that and divide it, since we have groups of three
I'm going to divide by 3, Control Enter,
0 and some decimals, 1 and some decimals, 2 and some decimals.
So F2, if I take the integer of that,
that'll give me 0s, 1s, and 2s, close parentheses,
Control Enter.
Now if I F2 and put that inside of EDATE,
I can lock it on the first date.
F4, comma, and that's the number of months to jump ahead,
close parentheses, Control Enter.
Those are the serial number dates.
If I add date, we can see we have exactly what we want.
Control Z. Now in the top cell I'm going to copy this.
Control C and through all these formulas.
Now, I don't have to copy them in sections.
I can just F2 in the top and right where I have the date
double click, Control V. Double click inside of end of month,
Control V. Now I can populate this all the way through
and I have exactly what I want.
I have December here, January here, and February here.
Now there's another way we can do that.
What if the condition wasn't a date?
Well, we sure couldn't use EDATE.
Instead we can consider this a look up range with nine cells.
And if we want to look up the first item, 1 1
1, and then the fourth item, 4 4 4, and then 7 7 7,
we need our incrementor inside of a lookup
function to give us 1s, 4s, and 7s.
So if we come down, and we'll use the same incrementor
but we'll adjust it a bit, Control C
and down here a few cells, equal sign,
Control V. When we Control Enter,
what if we multiply those results by three?
Then we'd get 0 3 6, which is almost what we want.
F2 times 3, Control Enter.
Now all we need to do is add 1.
F2 plus 1, Control Enter.
And now that tells us the relative position
within that range right there.
F2, so after the equals sign we use index.
Select F4, so an array, those are the dates, comma,
and there's the relative position of the item
to look up, close parentheses, Control Enter.
So now we can try Control C. And then in the top cell
F2 where we have EDATE.
Control V now we have index.
Inside of start date and end of month
Control V. And that formula will work whether or not
the condition is a date or something different.
Control Enter.
And that's our third method.
All right.
So we saw COUNTIFS with index, COUNTIFS with EDATE,
and then of course, we saw the just COUNTIFS
and the sledgehammer method where
we had to copy it over and move the cell reference.
All right.
If you like that video, be sure to click that thumbs up,
leave a comment, and subscribe because there's always
lots more videos to come from Excel is Fun.
And be sure to check out these future videos, including
maybe even a DAX one.