I've tried looking at all the prior threads, but nothing seemed to solve this the way I was hoping.
I've got two columns: A has full dates, and B has one of three events. I wanted to build a table that counts the number of events (in three columns), by years (in rows).
Yes, the simple way is to create another column and extract the year from the date [Year(A#)], then use COUNTIFS on Columns B & C, and truth be told I've already got my table, but I was looking to see if I could have done it using just the countifs, where one criterion finds the YEAR within the date in column A and the other finds the event in column B.
Your profile says Excel 2003, I assume you have a later version to use COUNTIFS.
You could use the start and end dates of the year, i.e. to count event1 in 2008
=COUNTIFS(A:A,">="&DATE(2008,1,1),A:A,"<"&DATE(2009,1,1),B:B,"event1")
or you can use SUMPRODUCT in any version
=SUMPRODUCT((YEAR(A$2:A$100)=2008)*(B$2:B$100="event1"))
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks