Straight Dope Message Board > Main Excel help, maybe math question
 User Name Remember Me? Password
 Register FAQ Calendar Mark Forums Read

 Thread Tools Display Modes
#1
01-22-2010, 04:22 PM
 Chessic Sense Guest Join Date: Apr 2007
Excel help, maybe math question

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.
#2
01-22-2010, 04:38 PM
 Ruminator Guest Join Date: Dec 2007
Quote:
 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.

Quote:
 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.

Code:
``` S M T W T F S
R?             100
J              120
X              115
9 8 2 8 4 5 3```
#3
01-22-2010, 05:42 PM
 Duke Guest Join Date: Jul 1999
Quote:
 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.
#4
01-22-2010, 05:50 PM
 Bottle of Smoke Guest Join Date: Jun 2000
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.
#5
01-22-2010, 05:51 PM
 Bottle of Smoke Guest Join Date: Jun 2000
Curses! Beaten to the punch!
#6
01-22-2010, 05:53 PM
 Ruminator Guest Join Date: Dec 2007
Quote:
 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.

Last edited by Ruminator; 01-22-2010 at 05:57 PM.
#7
01-22-2010, 06:16 PM
 Chessic Sense Guest Join Date: Apr 2007
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.
#8
01-22-2010, 06:21 PM
 Ruminator Guest Join Date: Dec 2007
Quote:
 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.
#9
01-22-2010, 06:36 PM
 Chessic Sense Guest Join Date: Apr 2007
Quote:
 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. Code: ``` 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:

Code:
```R 1 0
J 0 1```
has the same row and column totals as
Code:
```R 0 1
J 1 0```
So if the solution isn't unique, I don't see how it could be deduced.

Last edited by Chessic Sense; 01-22-2010 at 06:37 PM.
#10
01-22-2010, 06:43 PM
 Chessic Sense Guest Join Date: Apr 2007
Quote:
 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:
Code:
``` 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?

Last edited by Chessic Sense; 01-22-2010 at 06:44 PM.
#11
01-22-2010, 07:12 PM
 CookingWithGas Charter Member Join Date: Mar 1999 Location: Tysons Corner VA Posts: 9,329
Quote:
 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.

Last edited by CookingWithGas; 01-22-2010 at 07:12 PM.
#12
01-22-2010, 11:54 PM
 Ruminator Guest Join Date: Dec 2007
Quote:
 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)
#13
01-23-2010, 02:39 PM
 Chessic Sense Guest Join Date: Apr 2007
Aha! It works now. The quotes were indeed the problem. Thanks, all!

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     Straight Dope Chicago     General Questions     Great Debates     Elections     Cafe Society     The Game Room     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit Side Conversations     The Barn House

All times are GMT -5. The time now is 05:27 PM.

 Contact Us - Straight Dope Homepage - Archive - Top

Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?