Merging formula!

[krispatterson]

My main problem / lack of knowledge with Excel is getting formulas that look at multiple things to work. I really struggle with it... I've got 2 different formulas, but have no idea how to make it work as one...

data is as follows:

	A	B	C	D	E (Chris)	F (Joe)
1	05/01/17	Joe	1%	01/12/16
2	15/01/17	Chris	1.25%	01/01/17
3	12/12/16	Chris	1.5%	01/02/17
4	02/01/17	Chris	1.5%
5	19/12/16	Joe	1%
6	22/01/17	Joe	1.5%
7	27/01/17	Chris	1.25%

<tbody>
</tbody>

Individual formulas I'm using are:

=COUNTIFS(A:A,">="&D1,A:A,"<"&EDATE(D1,1),B:B,"chris")
This counts how many instances of "chris" occur in a month date range.

=AVERAGE(C:C)
This averages the percentages of C.


What I'm trying to achieve in E1 is a formula that:
> looks at all dates in 12/16
> filters for just "Chris" / "Joe"
> averages out the column C

So, results:
> E1 will be 1.5%
> F1 will be 1%
> E2 will be 1.33%
> F2 will be 1.25%


Is that possible? Thanks :D

 

[Dave Patton]

Try Averageifs

=AVERAGEIFS(C2:C8,B2:B8,E1,A2:A8,">="&D2,A2:A8,"<"&D3) or
=AVERAGEIFS(C2:C8,B2:B8,E1,A2:A8,">="&D2,A2:A8,"<="&EOMONTH(D2,0))

 

[Jonmo1]

Try averageifs

=AVERAGEIFS(C:C,A:A,">="&D2,A:A,"<"&EDATE(D2,1),B:B,"chris")

 

[krispatterson]

thank you both, worked perfectly

 

[Jonmo1]

You're welcome.