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

microsoft excelworksheet-function

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

Best Answer

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.

Related Question