Excel gurus - quick Q

I think what you are looking for is called a PivotChart.  It should be able to do a count by year quite easily

for example this from about 4 mins onwards.  Set it to show 'count of year'

Excel pivot table and pivot chart - YouTube

Is there a way that Excel will total only those years that appear in my column.

yes, you can use an index and an array formula to create a list which is reduced to one instance of each year. that can form the left hand of your count table and the right column can do the lookup.

https://exceljet.net/formula/extract-unique-items-from-a-list

It took me a couple of goes to follow that link, you need to pay attention to cell references.

its works very effectively once its set up. I use it all the time to do what a pivot table probably could also do 😀

Sounds like a histogram? Does this help?

Instructions

hmm i think the desire is for each year to have a seperate count?

I want to create a simple bar graph of number of publications per year.

Sounds like a histogram. There’s a built in tool to do the counting and plotting.

My understanding a histogram uses bins to count.

Where as the OP wants a bargraph with a count of each year, but only the relevant years ie no gaps in the bar graph.

If i have misunderstood and the reluctance is only the number of lookups required is daunting then a histogram will totally do it by a long list of bins rather than a lookup I completely agree.

I'm with leffeboy on a Pivot Table then create the graph from there.

If you can copy/paste the 'year' column into a new sheet, you can then select the whole column and sort by ascending values. You can then select Subtotals... from the Data menu and you will get a dialogue that lets you select 'At each change in: year', 'Use function: count'. If you also check the 'Replace Current Subtotals' and 'Summary Below data' boxes you will get a count by year, but it still won't be formatted well to make a histogram. However, you will also have 3 groups (look on the top left of your sheet). If you click to close the second group you will be left with a nice table showing the year and total for each year from which you can plot a histogram. Hope that makes sense...

Thanks folks, some ideas for me to try this morning.....

Pivot Table to get the count of the years then a bar chart to visualise it.

Isnt a pivot chart / table with a non-distinct count what you need. Might need to do a bit of prep to get to just the year, should work though.

Quick and dirty way I'd do it:

If the dates of each article are in Column A rows 1:200
In column B list the years (E.g. 1950, 1951, ... 2022) this acts as the bins for your histogram
Then in C1, type =countif($a$1:$a$200, b1)
The $ are important so excel doesn't change the range as you drag the formula down*
Double click C1 to copy for the length of your 'years' column.

* technically you could use a$1 not $a$1 since the column ref won't change when you drag down.

Highlight cols B and C and click graph.

Isnt a pivot chart / table with a non-distinct count what you need

Yes but the wonderful thing about Excel is that there are about a million different, convoluted, unmaintainable and unnecessary ways of achieving most things that can also be done quite simply. I'm surprised nobody's suggested VBA or PowerQuery yet...

Isnt a pivot chart / table with a non-distinct count what you need. Might need to do a bit of prep to get to just the year, should work though.

That

Quick and dirty does it - thanks @superficial.

I'm sure that pivot tables and charts etc do the same, but require more Excel mastery than I have.

Thanks all for the suggestions and input

My understanding a histogram uses bins to count.

Bin can be 1 in size - so each year in its own bin..

sorry, yeah that post wasn't very coherent.

My point was that i took from the OP that they didn't want empty bins. but i think that was more to do with multiple lookups. I wasn't disagreeing with you.

Yes but the wonderful thing about Excel is that there are about a million different, convoluted, unmaintainable and unnecessary ways of achieving most things that can also be done quite simply. I’m surprised nobody’s suggested VBA or PowerQuery yet…

Don't worry, someone will be along to say "Excel is the wrong tool for analysing data, you should learn R/python/COBOL/JavaScript. It's much easier".

Op: use a pivottable/pivotchart.

https://youtube.com/clip/UgkxoHIUsGC7jf0XaerzTe2AdVdEa_9iyopy

Don’t worry, someone will be along to say “Excel is the wrong tool for analysing data, you should learn R/python/COBOL/JavaScript. It’s much easier”.

I didn't say that, but since you brought it up: Using Excel to draw graphs is like using the handle of a screwdriver to bash nails in. It'll probably kind of work, but it's way harder than it needs to be and the end result looks shit.

Using Excel to draw graphs is like using the handle of a screwdriver to bash nails in. It’ll probably kind of work, but it’s way harder than it needs to be and the end result looks shit

So what should we be using? PowerBI, Tableau, Python? Any pointers on how/where to build an understanding to use those? Especially for those of us, as mentioned earlier, with limited mastery of more complex Excel formulae etc

So what should we be using?

Excel.

Excel

Just that.  Sometimes it is worth learning a new skill or two to make life a bit easier.  It takes under 40 secs in Excel using a Pivot chart.  Follow the link I put in the second post, 4 mins in.  It's super easy and is useful for way more things as well.   The other suggestions here are all good (well apart from Cobol) but this is pretty much exactly what PivoCharts are for

edit:maybe not