I'm a layman and further I think this is still fairly new research, but it's my understanding that it is aerobic, not anaerobic, exercise that provides the cognitive benefits you're after.
Aerobic exercise uses oxygen to convert glucose into energy, but when the body consumes more energy than can be produced this way (you can only breathe so hard) it resorts to a different process which does not have the same oxygen demands but also produces lactic acid and causes your muscles to burn and become stiff (though I think there is still some theory competition around the exact role/s lactic acid has).
I'm not certain whether the mechanism of action is understood, but I think it's perhaps possible that the neurogenesis (the forming of new neurons) observed in people that regularly engage in aerobic exercise is due to improved blood flow into the narrowest blood vessels in the brain due to the temporary increase in blood pressure. Medication intended to reduce blood pressure is apparently a potential cause or exacerbator of dementia in some people because there simply isn't enough pressure to continue forcing blood into these blood vessels as one ages.
Research has been done comparing cognitive activity to 15 minutes of aerobic exercise 3 times per week with both being excellent, cardio being a statistically significant improvement over cognitive activity, and only a tiny potential further improvement when the two are combined.
I'm still planning on keeping both in my schedule, though.
Morale of the story? If you're feeling a burn in your muscles instead of your lungs you're probably not going to get the results you're looking for, but clearly you can do both aerobic and anaerobic exercise if you're so inclined.
Edit: Here's an article that explains there's more to it than I covered in my answer.
And here's a research paper comparing high intensity interval training (HIIT) and resistance training to aerobic exercise.