count sumproduct in data range only with condition

Sorry, I have to deal with a revamped spreadsheet and simply am lost right now...

I have one sheet called "Data" with the raw data needed for this report and necessary columns set as dynamic ranges to make formulating easier.

Dynamic Ranges:
StaffID = staff ID of who enrolled the account
EnrollDate = date of the account being enrolled
Email = account email address

Sheet: Daily summary
on my summary sheet in cell G2 I have a dropdown list with values ranging from Jan to Dec (MMM format), which should then select me the accurate months only data.

Problem I:
A15 has the staff ID code (eg. FOVicky). Now I need to calculate how many accounts have been enrolled in the in G2 selected month, and how many of those were enrolled by the staff defined on column A. Thats still OK but I still have problems with the accuracy of the current formula as it seems to somehow double the entries. I believe it somehow is connected to the date format since I am using dd/mm/yyyy but am generating the formula from a MMM formatted date...

Please Login or Register  to view this content.

Problem II:
once the number of accounts is accurate, I need to show the number of enrolled accouts which have an email address.

Please Login or Register  to view this content.

Again I smell an issue with the date formatting since those results also seem to be double then actual.


Sheet: Annual Summary

Problem I:
I am looking for a way to sum up a sumproduct. sounds stupid (and it is), but basically I am looking for a way to sum up financial figures of the enrolled accounts enrolled in a specific month as defined in MMM format in cell I2.

Please Login or Register  to view this content.

I tried replacing the sumproduct with a simple SUM and keeping it in an array, but without success...

Problem II:
Since the sales managers are seperated in teams, I would need to keep on eye on their email capture percentage. I managed to generate the teams account enrolment figure, but I struggle with getting an average for their email capture.

Please Login or Register  to view this content.

I am not using dynamic ranges for this, therefore would hope to get a suggestion on how I can keep this figure updated in case I have new staff joining. suggestions maybe?

A sample workbook is attached with 3 months modified data. I would greatly appreciate if someone could have a look and point in the right direction. All necessary adjustments are highlighted in yellow.
Thanks a lot!

A2k


new sample.xlsx

Really nobody?

Thank you,
A2k

Hi

I notice. Name Manager got some double same word. This might what course it. It better have 1 word instead 2 same word.

Then again not 100% But I had a good search about Name Manager with 2 same word. Nothing so far.

thank you. I checked the named ranges and even though they actually consist out of two words, they are written as one...

Thanks,
A2k