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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E (Chris)[/TD]
[TD]F (Joe)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/17[/TD]
[TD]Joe[/TD]
[TD]1%[/TD]
[TD]01/12/16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]15/01/17[/TD]
[TD]Chris[/TD]
[TD]1.25%[/TD]
[TD]01/01/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/12/16[/TD]
[TD]Chris[/TD]
[TD]1.5%[/TD]
[TD]01/02/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/01/17[/TD]
[TD]Chris[/TD]
[TD]1.5%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19/12/16[/TD]
[TD]Joe[/TD]
[TD]1%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22/01/17[/TD]
[TD]Joe[/TD]
[TD]1.5%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]27/01/17[/TD]
[TD]Chris[/TD]
[TD]1.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.