Count based on rows and columns

**statenja** · 06-23-2009, 04:25 PM

I have a data table with two columns (surveillance #, date). I would like to count the number of times a particular surveillance # occured during a particular month, without doing a pivot table. In the attached example cell E2 i have the following equation

=LOOKUP(2,1/(($D2=$A$2:$A$50)*(MONTH(E$1)=MONTH($B$2:$B$50))*(YEAR(E$1)=YEAR($B$2:$B$50))))

which results in a value of 1, but not a count of the number of times surveillance #1 occurs in the month of January. Can someone point me in the right direction for how to make this return a count?

Any help is appreciated.

Thanks!

**NBVC** · 06-23-2009, 04:31 PM

Change formula to:

=SUMPRODUCT(($D2=$A$2:$A$50)*(MONTH(E$1)=MONTH($B$2:$B$50))*(YEAR(E$1)=YEAR($B$2:$B$50)))

**zbor** · 06-23-2009, 04:33 PM

Try this in E2 (and extend):

=SUMPRODUCT(--(MONTH($B$2:$B$50)=MONTH(E$1));--($A$2:$A$50=$D2))

(replace ; with , if needed)

**statenja** · 06-23-2009, 04:40 PM

Great!!! Thank you very much.

**NBVC** · 06-23-2009, 04:57 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save