Average with Criteria

[kumara_faith]

Hi,

I have the following in Table 1:

Table 1
Month	Branch	Staff	Rate
Jan	Texas	Javier	95%
Jan	Texas	Javier	0%
Jan	Texas	Javier	97%
Jan	Texas	Javier	0%
Jan	Colorado	Randall	93%
Feb	Texas	Javier	94%
Feb	Texas	Javier	91%
Feb	Oklahoma	Betty	85%
Feb	Virginia	Lupo	87%
Feb	Colorado	Randall	90%
Mar	Texas	Javier	93%
Mar	New York	Jenna	85%
Mar	Texas	Javier	86%
Mar	Virginia	Lupo	91%
Mar	Colorado	Randall	94%

In the following , I have the summary in Table 2 by month:

Table 2
Month	Branch	Staff	Rate
Jan	Texas	Javier	48%
Jan	Colorado	Randall
Feb	Texas	Javier
Feb	Oklahoma	Betty
Feb	Virginia	Lupo
Feb	Colorado	Randall
Mar	Texas	Javier
Mar	New York	Jenna
Mar	Virginia	Lupo
Mar	Colorado	Randall

In the column for Rate, I am trying to calculate the average for each employee. For Javier, the average for Jan is actually 96%. However, my current formula is calculating as 48% since it includes the zero in the range. Is there a way to modify the formula for the correct result ? Appreciate the help.

=AVERAGEIFS(E3:E17,B3:B17,G3,C3:C17,H3,D3:D17,I3)

 

[zero269]

I tested your values, and used three criteria:

 

[zero269]

If the RIGHT Table is showing the Averages you were expecting, then tailor your formula accordingly.
Using Cell ranges can be used, but not the best solution for a growing data set.

I used the Rate criteria only for values greater than zero. Before that, I too saw 48% for javier...
Out of curiosity, should a zero value even be in there considering all three have the same Month and State...?
Any way to combine your source data to so you have only one row for Javier in JAN for Texas?

 

[zero269]

Month	Branch	Staff	Rate		Month	Branch	Staff	Rate
Jan	Texas	Javier	95%​		Jan	Texas	Javier	96%​
Jan	Texas	Javier	0%​		Jan	Colorado	Randall	93%​
Jan	Texas	Javier	97%​		Feb	Texas	Javier	93%​
Jan	Texas	Javier	0%​		Feb	Oklahoma	Betty	85%​
Jan	Colorado	Randall	93%​		Feb	Virginia	Lupo	87%​
Feb	Texas	Javier	94%​		Feb	Colorado	Randall	90%​
Feb	Texas	Javier	91%​		Mar	Texas	Javier	90%​
Feb	Oklahoma	Betty	85%​		Mar	New York	Jenna	85%​
Feb	Virginia	Lupo	87%​		Mar	Virginia	Lupo	91%​
Feb	Colorado	Randall	90%​		Mar	Colorado	Randall	94%​
Mar	Texas	Javier	93%​
Mar	New York	Jenna	85%​
Mar	Texas	Javier	86%​
Mar	Virginia	Lupo	91%​
Mar	Colorado	Randall	94%​

 

[kumara_faith]

Hi zero269,

Thank you for your solution and it worked. Appreciate it and have a great dat ahead.