CODEWITHSUNDEEP
exceldateexcel-formulaexcel-2010
BGDev
BGDev

Reputation: 135

Conditioned Date Ranges-Month&Year table Excel

The Setup: (Dummy setup)

Have a Data Set with Columns for Year and Month

Year Column: 2014,2013,2014,2014,20113,2014... and so on in that pattern

Month Column: 1,12,3,4,5,1,2,3,2,2,11,1,1,3,2.. and so on in that pattern

Score: 2,5,6,7,10,13,14,2,5,1,5... and so on in that pattern

Year,month and score are Paired: (2014,1,2) , (2013,12,5), (2014,3,6)... and so on in that pattern

I would like to execute a COUNTIF if the data fallS in Q1 of 2014 using an excel function.

right now my function looks like this:

COUNTIF(year,MAX(year),month,"<="&MAX(IF(year=MAX(year),month,0)),month,">="&(MAX(IF(year=MAX(year),month,0))-2))

This does not work correctly for some reason. It evaluates to zero. But if i click on the (fx) button it shows that it evaluates to a non-zero value.

Question - Given the fact that Month and Year are in 2 columns how do i use an Excel function to selectively count data from a given Quarterly range.

Upvotes: 0

Views: 85

Answers (1)

Bijan
Bijan

Reputation: 8602

The function you are looking for is SUMIFS()

=SUMIFS(C2:C7,A2:A7,F2,B2:B7,">="&(F3*3-2),B2:B7,"<="&F3*3)

The F3*3-2 and F3*3 coverts the Quarter # into the corresponding month numbers. I.e. Q4 will be months 10,11,12.

enter image description here

Upvotes: 1

Related Questions