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.
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:
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 will perform faster then the
SUMPRODUCT but it does require the extra column.
If you want to add another column, you will use the
Add the other column with
=COUNTIFS() as follows:
COUNTIFS should do it for you. Set one of the conditions equal to month desired and the other to name.
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
For pretty dates, use
ex. A1 = 5/6/2013
=Month(A1) 'returns 6 =Text(A1,"MMMM") 'returns "May"
- Add Month in column C
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
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.
Our Awesome Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor