PDA

View Full Version : How to apply Average formula with multiple criterias



abhay_547
06-22-2011, 08:38 PM
Hi All,

I need a help on Average formula .i.e. I want to find the average of numbers present in a column based on multiple criteria. Here is what I have. Column A has Category .i.e. Deliver and Receive, Column B has Dates, Column C has Amounts. and I want to get the average of Amounts between two dates for each category separately for e.g. row I9 has Start date, row J9 has end date and L9 has Category so I want the average formula to look into column A for category first then in column B the start date first and then the end date and take the average of all the numbers which are there in column C .i.e. Amount column. Enclosed is the worksheet for your reference.

Thanks a lot for your help in advance. :)

Bob Phillips
06-23-2011, 02:03 AM
Try this ARRAY formula

=AVERAGE(IF(($B$2:$B$30>=J9)*($B$2:$B$30<=K9)*($A$2:$A$30=L9),$C$2:$C$30))