COUNTIFS and SUMPRODUCT Problem/Frustration

See attachment file. I am trying to calculate the number of times a name appears from Column N during the specific year (2014 or 2015) of the many dates contained in Column A. I just cannot come up with the correct formula or syntax. I have figured out how to do this based on the manual entries in the blue cells. Just can't figure out the formulas to plug into the pink cells. Thanks in advance.

I think this may be what you're looking for:

=SUMPRODUCT((N2:N26=D26)*(A2:A26 >= D27)*(A2:A26 <= D28))

This is an array formula, so enter the formula and confirm it with CTRL + SHIFT + ENTER. You'll know you've done it correctly if Excel puts {} around your formula. Cheers!

I see how that works in the blue cells, but what I am trying to create are formulas for the pink cells that will dynamically update as more row data for 2015 are added. Thanks!

We can use the same principle to achieve that. Let's make your Trips-2014 box say 2014, and Trips-2015 say 2015. Now we can use those to match against the year for column A:

=SUMPRODUCT(($N$2:$N$26=$A33)*(YEAR($A$2:$A$26)=B$32))

Again, it is an array formula. That should work in any cell in your pink table to return the correct answer, even if you add year or street names.

Perfect!!! Thanks!!

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


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

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


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Dang it, I keep doing that. Thanks, daddylonglegs