Chart two events over time

Hello everyone this is my first post. I have spent quite a while trying to work things out here.

I am trying to create a chart and not a pivot chart of how often two events happen in a year (grouped monthly).

IN the attached file sheet query1 is the information i get from an access database. I would love to be able to work directly from this.

sheet 2 is me trying different formats.

Thank you for your help it is much appreciated.

Thank you for your help it is much appreciated.

Chart 6 seems to be a pivot chart of what i want.

In words I would like the x-axis to have the months and then I would like each month to have two columns showing the number of "contacts" and the number of "projects" that have a date within this month.

Is it possible to do this without a pivot chart?

Hi Darbid

Wilkommen an Bord, oder so aehnlich.

I've attached a spreadsheet with a solution that works without a pivot table, but uses the Sumproduct function instead.

On the new tab Sheet1 I have manually entered dates in column A, always entering the first of the month. If you want, you can format these dates to show only the month and the year.

The Sumproduct formula in column B looks at the date in column A and counts how many entries in Column A in the query tab
- have the same month, AND
- have the same year, AND
- have a "TRUE" in column B in the Query tab

The Sumproduct formula in column C does the same, but looks at query columns C and D for date and TRUE.

From the resulting table you can create your graph.

If you update your query dynamically, you may want to look at dynamic range names that adjust to your data as it grows and enter the range names instead of the absolute references in the Sumproduct formula.

There are lots of threads in these forumns and at www.mrexcel.com about dynamic range names, or just google it.

Hope that helps.

Viel Spass beim Ausprobieren ....

Teylyn

Originally Posted by teylyn

Wilkommen an Bord, oder so aehnlich.Teylyn

Alle Deutschen sind in NZ oder Australien und der Australier ist in Deutschland.

Anyway thanks for the introduction.

And wow thanks for spoonfeeding me and doing it all for me. That was amazing.

That forumula you used I will have to study and then change as you suggested so that it can accept access dumps.

I was actually hoping I could get away with no manipulation of the data (ie the new sheet you had to make) as that would mean I could directly make this chart in access. But I am slowly realising that is not going to happen.

Thanks again.