Excel help, maybe math question

[Chessic Sense]

My boss needs to make a report that breaks down how many items a particular employee processed in each day of the week. The actual task has been solved w/ a pivot table (which are the devil's brethren) but I still want to know how I could do it first way I tried:

Each item has a name and date associated with it. The name is in column A and the date is in column B. So obviously there are several occurences of each name and each date.

I want an equation that I can put in a different cell that's basically "Count if A=Ron and B=Sunday". I can't use a VLookup because it'll only return the first value it finds. I can't use Countif because it only takes in one criteria, and I need both A and B to be the same thing. Something tells me I can concatenate the columns, then refer to the result, like =if(concatenate(a2, b2)=[name and date], 1, 0) and then try to count those, but I can't figure it all out exactly.

So what's the right formula? Again, I want the cell to return how many were done by, say, Ron on Sunday by counting how many times Ax=Ron and Bx=Sunday, then do this for each person and day.


The math question:
If I know how many Ron did (and every other employee), and I know how many were done on Sunday (and every other day), is there any way to know how many Ron did on Sunday? I don't think there is for reasons I'll go into later if need be.

[Ruminator]

Originally Posted by Chessic Sense

I want an equation that I can put in a different cell that's basically "Count if A=Ron and B=Sunday". I can't use a VLookup because it'll only return the first value it finds. I can't use Countif because it only takes in one criteria, and I need both A and B to be the same thing. Something tells me I can concatenate the columns, then refer to the result, like =if(concatenate(a2, b2)=[name and date], 1, 0) and then try to count those, but I can't figure it all out exactly.

If I read your description correctly, the way I do it is an array formula with nested functions.

For 2 columns, the template would look like this: INDEX(MATCH(IF()))
The inner IF() function is what requires the array formula

I think you can see that the pivot table method is much easier.

The math question:
If I know how many Ron did (and every other employee), and I know how many were done on Sunday (and every other day), is there any way to know how many Ron did on Sunday? I don't think there is for reasons I'll go into later if need be.

Doesn't sound like you have enough info here to solve the equation. You need to know the distribution for the all the other employees (not Ron) for all other six days (not Sunday) to solve specifically for Ron on Sunday.

 S M T W T F S
R?             100
J              120
X              115
 9 8 2 8 4 5 3

[Duke]

Originally Posted by Chessic Sense

I want an equation that I can put in a different cell that's basically "Count if A=Ron and B=Sunday". I can't use a VLookup because it'll only return the first value it finds. I can't use Countif because it only takes in one criteria, and I need both A and B to be the same thing.

Ah, then what you need is a CSE array.

Say :Employee Name" is in column A, "Weekday" is column B, and "Hours Worked" is column C. To get the number of days Ron worked on Sunday, type in the following formula:

=sum((A1:A10="Ron")*(B1:B10="Sunday")*1)

and hit Control Shift and Enter all at the same time. To get the number of hours Ron worked on Sundays, type in the following formula:

=sum((A1:A10="Ron")*(B1:B10="Sunday")*(C1:C10))

and hit Control Shift and Enter.

CSE arrays are actually pretty cool. I could go on but I'll let Mr. Excel explain them.

[Bottle of Smoke]

Ruminator is right -- the best way to handle this outside of a pivot table is with an array formula. If you use SUM inside an array formula, you can specify as many criteria as you want and it will count all occurrences where the criteria are met.

So in your example, if you had names in column A and days in column B (and I'm assuming 100 rows of data, just for fun) your formula would be:

{=SUM((A1:A100="Ron")*(B1:B100="Sunday"))}


Standard disclaimer on array formulas:
Make sure you hit Crtl+Shift+Enter to enter the formula, not just Enter. That will put the little squiggly brackets in for you and tell Excel you are crteating an array formula.
Make sure your ranges are all the same length (i.e. A1:A100, B1:B100, etc. You'll get wonky results if the ranges are different lengths).
These things get a lot more easy to read if you name your ranges, especially as the formulas get longer. So instead of A1:A100="Ron" you have FirstName="Ron"

This is actually one of the most frequently used formulas in my arsenal at work. If you want to add more criteria, just add another *(C1:C100="Another Criteria") in there.

[Bottle of Smoke]

Curses! Beaten to the punch!

[Ruminator]

Originally Posted by Duke

=sum((A1:A10="Ron")*(B1:B10="Sunday")*1)

Just to add a comment...

I've not met one non-computerscience person that understood that the above formula multiplies boolean TRUE/FALSE/0/1 values.

The newer SUMPRODUCT() function hides this and can often replace SUM() with CSE formulas.

But your answer did point out a flaw in my post. I was providing the ability to find the cell that met the criteria, not count (sum) them.

[Chessic Sense]

One of the first things I tried too was using the Sum. But the problem I get is that it counts all the Rons and then counts all the Sundays and adds them together. So if Ron did 70 in a week (10 a day), and everyone did a total of 100 on Sunday (ten employees, let's say), then I get 170, not just 10.

In other words, I keep getting the Union of Ron and Sunday, not the Intersection of Ron and Sunday.

When I tried pasting in the array formula, I got "0" as a result, so obviously I'm not getting something.

[Ruminator]

Originally Posted by Chessic Sense

When I tried pasting in the array formula, I got "0" as a result, so obviously I'm not getting something.

Did you press Ctrl+Shift+Enter instead of just Enter?

Also, is column B actually the word "Sunday" or is it a date (mm-dd-yyyy) that's been formatted to display "Sunday" ? If so, you need to use the WEEKDAY() function on column B to extract out the day-of-the-week and test on that condition.

[Chessic Sense]

Originally Posted by Ruminator

Doesn't sound like you have enough info here to solve the equation. You need to know the distribution for the all the other employees (not Ron) for all other six days (not Sunday) to solve specifically for Ron on Sunday.

 S M T W T F S
R?             100
J              120
X              115
 9 8 2 8 4 5 3

Well, the one piece of evidence that we do have is that the bottom row has to total the same as the side row. So at the very least, your random numbers aren't valid. I still don't think that changes anything, though. My reasoning is that:

R 1 0 
J 0 1

has the same row and column totals as

R 0 1
J 1 0

So if the solution isn't unique, I don't see how it could be deduced.

[Chessic Sense]

Originally Posted by Ruminator

Did you press Ctrl+Shift+Enter instead of just Enter?

Also, is column B actually the word "Sunday" or is it a date (mm-dd-yyyy) that's been formatted to display "Sunday" ? If so, you need to use the WEEKDAY() function on column B to extract out the day-of-the-week and test on that condition.

Yes, I did. I made a short list of fake names: Mike, Joe, Bambi, Ron, Jim, etc...
Then I made up numbers to go with them: 1, 2, 3...

The exact formula I put in is:
=Sum((A1:A15="Mike")*(B1:B15="1")*1)

In the actual list, it's mm/dd/yy, but I'm fine with just referencing a cell that contains our date. For example, in the table:

 1/15 1/16 1/17
R
J
M

In the cell B2, I'd just reference A2 (R) and B1 (1/15) for my name and date.

But that part's not really important. The actual, real world work is already done. I just want to know the general plan and formula because I can't stand an unsolved puzzle. On another note, did you notice my union/intersection problem? What's the workaround for that?

[CookingWithGas]

Originally Posted by Chessic Sense

Something tells me I can concatenate the columns, then refer to the result, like =if(concatenate(a2, b2)=[name and date], 1, 0) and then try to count those, but I can't figure it all out exactly.

The best way to solve this is a pivot table. You would be doing yourself a huge favor to learn how to use them. They are possibly the single most powerful Excel feature. The next best way is the array formula as described.

The way you mentioned above will also work and is easy to understand, if a bit brute force.

You would create a new column with the formula

=CONCATENATE(A1,",",B1)

and copy to every row. Then you would put in a place of your choosing

=COUNTIF(C:C,"MIKE,SUNDAY")

You include the comma in the concatenation to add readability. In other situations used for something other than days of week where you don't know in advance all the possible values, it avoids subtle bugs.

[Ruminator]

Originally Posted by Chessic Sense

Then I made up numbers to go with them: 1, 2, 3...

The exact formula I put in is:
=Sum((A1:A15="Mike")*(B1:B15="1")*1)

If you entered 1,2,3... as raw digits into the cell with tho apostrophe at the beginning, your SUM() formula needs to compare B1:B15 to numbers with no quotes like this:

=Sum((A1:A15="Mike")*(B1:B15=1)*1)

[Chessic Sense]

Aha! It works now. The quotes were indeed the problem. Thanks, all!