AverageIFS with a Date range

Question

Here is my an example of the Data.  I would like to use an Average function to be able to Average the amount Column C excluding "software" and or "No".  If = "software" or no then it shouldn't be counted in the Average.  Also say I only wanted to capture the ones that are in March, what would be the best way to complete this task and what would it look like? Thank you!

         A              B             C        D

1   Software     No            2        03/02/11

2   Software     Yes          1        03/05/11

3   Software      NO          4        04/05/11

4   Software      Yes          5       03/07/11

5   Hardware     Yes          10     06/05/11

6   Hardware     No            1       03/21/11

7   Hardware     Yes          3       04/02/11

8   Hardware     No            0       05/05/11

9   Hardware    Yes            4      03/21/11

10 Hardware     No             5      03/17/11

11 Other           No             4       04/17/11

12 Other           Yes           3       05/21/11

13 Other           No             2       07/21/11

14 Other           Yes           15      03/31/11

Answer 1

shortma wrote:

Here is my an example of the Data.  I would like to use an Average function to be able to Average the amount Column C excluding "software" and or "No".  If = "software" or no then it shouldn't be counted in the Average.  Also say I only wanted to capture the ones that are in March, what would be the best way to complete this task and what would it look like?

=AVERAGEIFS(C1:C14,A1:A14,"<>software",B1:B14,"<>no",

D1:D14,">="&DATE(2011,3,1),D1:D14,"<="&DATE(2011,3,31))

If you literally mean "are in March" -- that is, you do not care what year -- then try the following array formula [*]:

=AVERAGE(IF(A1:A14<>"software",IF(B1:B14<>"no",IF(MONTH(D1:D14)=3,C1:C14))))

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.  Excel will display an array formula surrounded by curly braces in the Formula Bar, i.e. {=formula}.  You cannot type the curly braces yourself.  If you make a mistake, select the cell, press F2 and edit, then press ctrl+shift+Enter.

Answer 2

Hi, try

=SUMPRODUCT(--(A1:A5<>"Software"),--(B1:B5<>"No"),C1:C5/SUMPRODUCT(--(A1:A5<>"Software"),--(B1:B5<>"No")))

change range for yours