Excel 2010, How do I write this formula? Data is on a different worksheet, same workbook

Please help me with this formula:

(worksheet A) has dates in Column A for the whole year in this format (06/19/13) and several different names of people in Column B. Lets use the name (Brian) for this scenario.

On (worksheet B) in the same workbook, I need to write a formula to add the number of times (from worksheet A) the name Brian has appeared in a particular month.

I have tried different variations of COUNTIF, IF THEN, SUM, and can't figure it out. Any help would be greatly appreciated. I am not an excel wiz, but do the best I can with basic formulas. This one has me stumped. I tried to add images, but I am new to this site and need a “reputation” of 10 before I can do that, sorry.

UPDATE: Here are the images as described above. B16 on worksheet B is where I'm trying to add how many times Brians name was listed in May from worksheet A.

worksheet A

worksheet B

95B2PP5

Posted 2013-06-19T15:57:53.733

Reputation:

1which version of excel? for 2007 or later countifs should work but a pivot table might be easier – None – 2013-06-19T16:00:36.493

1Now you have rep to insert image .. – matzone – 2013-06-19T16:10:10.890

I'm using Excel 2010. I see I have the ability to add images now. Hang on a second while I figure out how to add it to this post. Thanks for your response. – None – 2013-06-19T16:12:54.030

Have you try in VBA ? – matzone – 2013-06-19T16:58:05.350

@matzone ... I have not. I wouldn't know where to start. – None – 2013-06-19T17:20:47.457

See my post below. This is what PivotTables are meant for so please use them.

– KyleMit – 2013-06-19T19:59:57.770

Answers

The COUNTIF and COUNTIFS functions take a range as their first argument, and will not allow you to operate on this range i.e. COUNTIF(MONTH(A:A),6) will throw an error.

Short of changing the layout of your data in your sheet, your only option is to use SUMPRODUCT like so:

=SUMPRODUCT(--(MONTH(Sheet1!A1:A25)=6),--(Sheet1!B1:B25="Brian"))

This will count how many times Brian appears in June. Note how -- is used to convert boolean TRUE and FALSE values (which cannot be multiplied by SUMPRODUCT) to numerical 1 or 0.

Your other option is to insert another column in the source data:

A = Date    B = Names    C = "=Month(A)"

So in C1 you would enter =Month(A1). With this you can use:

=COUNTIF(Sheet1!A1:A25,6,Sheet1!B1:B25,"Brian")

The COUNTIF will perform faster then the SUMPRODUCT but it does require the extra column.

user2140261

Posted 2013-06-19T15:57:53.733

Reputation: 141

Ok. Hold one while I read these and make sense of it. Forgive me, I am not a wiz, but a quick learner, usually. – None – 2013-06-19T16:32:31.897

If I create another column as indicated above, and enter "=Month(A1)", do I duplicate that formula for the whole year in that column by dragging the small black square in the lower right of the cell? – None – 2013-06-19T16:41:30.060

I'm all for the extra column, because I can hide it. I'm just confused on a couple things. Do I type it exactly as indicated and if so, does that same exact formula go in all cells in Column C for the whole year? – None – 2013-06-19T16:51:05.067

@95B2PP5 In C1 Enter =Month(A1) then drag the formula down with the little black square in the bottom right. AS you drag it down you should notice the formula will change in each cell to A plus that row number. – user2140261 – 2013-06-19T18:33:37.060

I just saw your repsonse from 3 minutes ago... sorry.... so after I do that in column c, don't I need to reference that coluimn in my countif formula?......Sir, are you able to help me with this any further? I am not unclear on the solution you provided and suspect I need further detailed instructions on how to write this formula on a seperate worksheet from where the data worksheet. @user2140261 – None – 2013-06-19T18:37:20.297

I followed this to the letter, and still unable to get it to work using the countif formula you provided. Should there be a reference to Column C in the Countif Formula? – None – 2013-06-19T19:00:53.537

I like the --<boolean expression> trick to convert from boolean to 1 or 0. I've seen IF(<boolean expression>,1,0) before, but -- is more succint and actually more readable once you get used to it. Actually, let me edit your answer to clarify this (and many other things...) – Jean-François Corbett – 2013-06-19T19:30:19.937

If you want to add another column, you will use the COUNTIFS() formula.

Add the other column with =month()

And use =COUNTIFS() as follows:

enter image description here

Jerry

Posted 2013-06-19T15:57:53.733

Reputation: 4 716

COUNTIFS should do it for you. Set one of the conditions equal to month desired and the other to name.

Joe

Posted 2013-06-19T15:57:53.733

Reputation: 242

you can't use any function inside of a count if that will modify the source data. He needs the month from the date and if you try to enter =CountIf(MONTH(A:A),6) you will receive an error. – user2140261 – 2013-06-19T16:25:38.240

Just want to chip in that what you really want here is to use PivotTables. They don't always work great with values as DateTimes, but you can easily extract out the month part of the date with the Text function.

For pretty dates, use Text, not Month

ex. A1 = 5/6/2013

=Month(A1)
'returns 6
=Text(A1,"MMMM")
'returns "May"

Add Month in column C
Use Text(cell, "MMMM") and drag to the bottom
Add a pivot table with Name in the row labels, month in the column labels, and a count of date in values

Pivot Table

Less code is better code. Let Excel implement the conditional iffs for you so that there's less for you to mess up and more flexibility if you want to change the format of the report.

KyleMit

Posted 2013-06-19T15:57:53.733

Reputation: 3 308

For specific uses this is very valuable I almost exclusively tell people to use Pivot tables for any type of data consolidation. The issue falls into play if the asker doesn't want his end user to see all the data, and to be able to simply enter a Month and a name a retrieve a value. – user2140261 – 2013-06-19T21:09:42.743

Then you can use =GETPIVOTDATA("Date",$A$9,"Name","Brian","Month","May") and allow them to pass in Brian and May as values from a combo box. – KyleMit – 2013-06-19T21:13:50.613