Register (or login) on our website and you will not see this ad.

I'm trying to get an Excel (Excel 2010) formula for the following task:
Col.1 contains dates
Col.2 contains values
I need Cols.314 to contain the number of values in a range for each of the 12 months
I can achieve it by having another column  named Month  containing the Month value of Col.1 and then have in Col.3 the formulae
=COUNTIFS(Month,1,Values,">0",Values,"<=10")
=COUNTIFS(Month,2,Values,">0",Values,"<=10")
and so on for each of the 12 months.
I'm trying to achieve that without the need for a separate Month column, such that something like
=COUNTIFS(Month(Dates),"=1",Values,">0",Values,"<=10")
would work.
Any thoughts?
Tony
We have more and more laws, and less and less enforcement


Your problem here is that the month function can't operate on a range.
The easiest ways to do this would be the way you currently are but put the months column either off to the right or on another sheet and hide it (so that people don't see the messiness) or expand the formula to:
=COUNTIFS(Dates,">=1/1/2014",Dates,"<1/2/2014",Values,">0",Values,"<=10")
=COUNTIFS(Dates,">=1/2/2014",Dates,"<1/3/2014",Values,">0",Values,"<=10")
etc (assuming you want a month in a particular year)


I have had fun with this and constructed a solution for something I wanted to do anyway which was to look at my solar results.
Unfortunately as Ian indicated I could not do it without the month column being present, though by right clicking at on the C at the top of the column and selecting hide, it becomes invisible.
My Solution is here if you want to have a look. Though I have to confess I did not totally picture what you were doing.
On my sheet
 The values to be analysed go in columns A and B down to the row 2000.
 It will work with just 1 row filled for checking (blank out the rest)
 Currently 134 rows are filled with randomly generated values.
 Fill the formula further down to experiment
 To make the grid access more rows modify the formula in D3. and fill the formula in column C further down.
 The values of the ranges can be modified by changing the numbers in D9 to D19
 The total at the bottom right should always match the number of rows.
Well I had fun and learnt a couple of new things so I hope it may be of use to you as well.
http://speedtest.net/result/2459383290.png
Solar Results
Nov 143, Dec 125, Jan 77, Feb 174, Mar 210, April 384, May 450,
June 485, July 512, Aug 433,
Max Day 25.2

Register (or login) on our website and you will not see this ad.


I need to study yours in more detail as I think I can learn a thing or two
Oddly enough my application is very similar.
Col.A contains dates, every day of the year is there, or will be as time goes by.
Col.B contains the minimum overnight temperature (F)
Col.C contains the minimum overnight temperature (C)  auto inserted by VBA
Then a block of cells to the right contain 12 columns, 1 per month; and 7 rows, 1 for each temperature range. You can see the spreadsheet here.
[As an aside, I quite like the formula I constructed in cell D1]
Tony
We have more and more laws, and less and less enforcement


OK, there is a way to do this without the months column. You need to use an array formula rather than countifs. The formula for cell W6 (december >30 and <40):
=COUNT(IF(MONTH(Dates)=12,IF(DegreesF>=30,IF(DegreesF<40,1))))
However, because it is an array formula you have to use CtrlShiftEnter to save the formula. The Dates range is one I created that covers all the dates in column A.
When you save it use CtrlShiftEnter it will add curly brackets  {}  to start and end of the formula.


Oh no!
I was planning to get on with some productive work, now you have opened a new can of worms for me to learn about.
http://speedtest.net/result/2459383290.png
Solar Results
Nov 143, Dec 125, Jan 77, Feb 174, Mar 210, April 384, May 450,
June 485, July 512, Aug 433,
Max Day 25.2


LOL. To be fair they aren't that difficult once you get your head around them  that was about 10 minutes for me and until then I hadn't ever looked at array formulas. But I might well use them in future
My problem was I was given a challenge and I find it very hard to resist them and I do so love playing with Excel formulas.


I like the idea of using the named ranges.
When I was doing mine I was trying to leave it open to adding more values at a later date. In my case running the dates down, rather than up the column makes this easier. (As an aside it makes the axis on the graph go from left to right rather than right to left as well.)
Then by using a formula (countif) to see how how many rows have been filled, you can use a formula to write the ranges as well and place these in a cell.
To apply the range in another formula you use the indirect function to look in the cell containing your range.
My direct challenge when I created my grid, was to put a single formula in the top left cell and then use fill to put it in all of the other cells.
As a matter if interest do you have some sort of weather station device to do all the recording for you?
http://speedtest.net/result/2459383290.png
Solar Results
Nov 143, Dec 125, Jan 77, Feb 174, Mar 210, April 384, May 450,
June 485, July 512, Aug 433,
Max Day 25.2


Excellent! That works  not that I was doubting you
I'd tried to get an array formula, but I was trying to do it with COUNTIFS.
Like you I enjoy 'playing' with Excel, I always have been someone who likes numbers.
Tony
We have more and more laws, and less and less enforcement


Glad it works for you. Quite a neat little formula and something that will come in handy.
I started trying it with COUNTIFS and then realised that COUNTIFS is in many ways just the nonarray version of the array COUNT formula.


