Multiple Months and Figuring Numbers

Hello,

I am trying to get some ideas or thoughts that can help me figure out the best way to get data from a large data source that spans multiple months in some cases. I have a dataset that currently spans about 5000 rows. This dataset has (mainly) dates listed in the columns but also some other information I am needing to capture that can be dollars or numbers.

The issue I am having is that each of the dates will probably be in a different month than the previous one. So when I am counting how many times the date appears in the month (on one pivot table) it is never correct because I am basing it off another columns month.

The only way I have found that I can count how many times the date appears is having a pivot table per column. This means that I will end up having over 10 pivot tables which just seems like overkill and convoluted. To sum it up- I am trying to get accurate numbers, but if I use one pivot table then its basing the numbers in a month from another column and giving incorrect numbers. If I use multiple pivot tables then the numbers are more accurate but is it really necessary to create a pivot table per header?

I know its confusing so I made a quick mock up. I didn't create 10 pivot tables but I made 4 as this should give you a glimpse of the issue am running into. I also consolidated the datasource just to make it easier on the eyes. Please correct me if this may be better in the pivot table area of this forum. I am not necessarily looking for pivot table help- just really anyway I can get the same results without making it to convoluted. Thank you all.

Thank you so much. That seems to be what I am looking for. Apologies, I have never altered a formula like this one. When I tried to transpose that formula into the my workbook it keeps giving a #VALUE error. This is what it looks like altered:

=SUMPRODUCT((Source!$B$2:$B$20000=F$7)*(MONTH(INDEX(Source!$A$2:$AP$20000,,MATCH($E9,Source!$A$2:$AP$2,FALSE)))=$I$7)*(YEAR(INDEX(Source!$A$2:$AP$20000,,MATCH($E9,Source!$A$2:$AP$2,FALSE)))=$G$7))

I think I changed it all to match: F7 is a drop down of the name / G7 is the year only / I7 is the Month in number form (9 for September) / E9 matches the header name on the 'Source' worksheet. Do you happen to know what I may be missing? I know its hard to tell but ive been messing with it for the past 30-45 minutes and its always just a #value error.

Thank you again!

Thank you again for the assistance. I tried to get it corrected but I still am running into that issue. I attached a screenshot of the top rows of the "Source" page and of the area that is showing #VALUE. I will be able to pull this up again on Monday morning.

Thank you again. I really really appreciate it.