Conditional Averageif

[PSG]

Hi Experts,


I need your help to find conditional average for my data. Here is link to the file http://kvisit.com/SyIu7AQ , I am looking to find the average of column3 based on data from column1 & column2. Average should be for the month of Feb & June while the for a, b, c and d.


Hope I made my query clear!


Prakash

 

[anupagarwal06]

I have used a helper column, you can change the required months in the cell in yellow. I have made provision for up to 4 months.


http://www.keepandshare.com/doc/3065791/conditional-averageif-xlsx-august-10-2011-10-44-am-10k?da=y


Hope this helps.

 

[Hui]

Do you want Feb & June Combined ?


If you want Feb and June seperately


H2: =AVERAGEIFS($C$1:$C$24,$B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,2,1),$A$1:$A$24,"<="&DATE(2011,2,28))

I2: =AVERAGEIFS($C$1:$C$24,$B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,6,1),$A$1:$A$24,"<="&DATE(2011,6,30))


Copy H2 and I2 Down


Now if you want to do the combined Average of Feb and June and do it with 1 formula


Put this in H2:


=(SUMIFS($C$1:$C$24,$B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,2,1),$A$1:$A$24,"<="&DATE(2011,2,28))+SUMIFS($C$1:$C$24,$B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,6,1),$A$1:$A$24,"<="&DATE(2011,6,30)))/(COUNTIFS($B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,2,1),$A$1:$A$24,"<="&DATE(2011,2,28))+COUNTIFS($B$1:$B$24,$G2,$A$1:$A$24,">="&DATE(2011,6,1),$A$1:$A$24,"<="&DATE(2011,6,30)))


Copy down

 

[PSG]

Thanx guys for your help!