Excel data analysis

[Tom_the_can]

I have columns of rainfall data variable numbers. I would like for example to count cells less than 5mm for each column for the year and get a total sum for that column of amounts below 5mm and above5mm. E.G. 3,2,2.5,1,1,1.5,4 = 19.5. Is this possible.  Would appreciate any help I can get. I have a limited amount of experience on Excel so answers need to be simple please.

Thank you

[#2]

@Tom_the_can 

You were very close. You'd already discovered the COUNTIF formula. Unless I'm missing something, all you needed to find was the SUMIF, which is much the same.

See attached.

[#3]

@Tom_the_can 

I'd separate source data with reporting, keeping all source data in one table with two columns (date and value). Date are for entire history.

Now you may pivot that table receiving aggregated data per month, quarter, year, etc. Calculated field could be added to PivotTable, however to simplify you may add them directly to source table. Result is like

[#4]

I agree with Sergei that this is the better approach.

[#5]

Sergei,
Many thanks for taking the time to respond to my question. It is really appreciated and has moved me a little further on my voyage of learning and discovery.
Tom-the -can

[#6]

Thanks for taking the time to respond. I shall investigate that option. I do however like Sergei's solution. Looks like I am going to be busy.
Than;ks.
Tom-the -can

[#7]

@Tom_the_can 

The solution you got from @Sergei Baklan  is far and away the more robust solution. I was being lazy, seeing how close you were even though the way you'd laid it out was sub-optimal. I'm a firm believer in creating basic tables for collecting the data and then using tools like the Pivot Table to analyze the data.

In effect, the way you'd laid it out, though more visually clear for data entry, has you doing a big part of the job of the analysis at the front end. Yours wasn't extreme, but it did mean you (and I, sticking with that layout) had to write a few individual formulas to get the result. Using the Pivot Table lets Excel do all that "heavy lifting"; all you need to do is collect the data. ALL you need to do.

So in your learning, I encourage you to watch some YouTube videos, or use websites like the following, on Pivot Table and other tools. Excel has a lot of power; we just have to learn to get out of its way.

https://exceljet.net/glossary/pivot-table