Average Revenue per month

[NYEXCEL1]

I need assistance putting together a formula in a separate column that will give me the total average retainer for each client based upon the number of months below. Since there are duplicate months i cannot simply do an average. Tried doing an average if formula but need to embed something that eliminates the duplicates. For example, Client be average should be 1000 (3000 / 3 months)

Thank you in advance



[TABLE="width: 327"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]Team[/TD]
[TD]Month[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]June[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]July[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]July[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]August[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]August[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]September[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]September[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]October[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]October[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]January[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]January[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]February[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]February[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]March[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]March[/TD]
[TD="align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]
`

 

[t0azt]

Your formula for Client B would be

=SUMIF(A2:A16,"Client B",D2:D16)/SUMPRODUCT((A2:A16="Client B")*(C2:C16<>"")/(COUNTIFS(C2:C16,C2:C16,A2:A16,"Client B")+(A2:A16<>"Client B")))

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Client[/TD]
[TD]Team[/TD]
[TD]Month[/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]June[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client A[/TD]
[TD]

500​

[/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]July[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

500​

[/TD]
[TD]

500​

[/TD]
[TD]

500​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]July[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]August[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]August[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]September[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]September[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Client A[/TD]
[TD]Team B[/TD]
[TD]October[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Client A[/TD]
[TD]Team A[/TD]
[TD]October[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]January[/TD]
[TD]

800​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]January[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]February[/TD]
[TD]

800​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]February[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Client B[/TD]
[TD]Team B[/TD]
[TD]March[/TD]
[TD]

800​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Client B[/TD]
[TD]Team A[/TD]
[TD]March[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

h2=IFERROR(AVERAGEIFS($D$2:$D$16,$A$2:$A$16,$G2,$C$2:$C$16,H$1),"") copy across and down

 

[VBA Geek]

perhaps you looking for something like this:

Excel 2013
	A	B	C	D	E	F	G
1	Client	Team	Month	Revenue	AVG
2	Client A	Team A	June	500
3	Client A	Team B	July	100		Client	AVG
4	Client A	Team A	July	500		Client B	1000
5	Client A	Team B	August	500		Client A	580
6	Client A	Team A	August	100
7	Client A	Team B	September	500
8	Client A	Team A	September	100
9	Client A	Team B	October	500
10	Client A	Team A	October	100
11	Client B	Team B	January	800
12	Client B	Team A	January	200
13	Client B	Team B	February	800
14	Client B	Team A	February	200
15	Client B	Team B	March	800
16	Client B	Team A	March	200
Sheet2

Cell Formulas
Range	Formula
G4	{=AVERAGE(IFERROR(1/(1/SUMIFS($D$2:$D$16,$A$2:$A$16,$F4,$C$2:$C$16,{"Jan*";"Feb*";"Mar*";"Apr*";"May";"Jun*";"Jul*";"Aug*";"Sep*";"Oct*";"Nov*";"Dec*"})),FALSE))}
G5	{=AVERAGE(IFERROR(1/(1/SUMIFS($D$2:$D$16,$A$2:$A$16,$F5,$C$2:$C$16,{"Jan*";"Feb*";"Mar*";"Apr*";"May";"Jun*";"Jul*";"Aug*";"Sep*";"Oct*";"Nov*";"Dec*"})),FALSE))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[NYEXCEL1]

VBA Geek, this could work. if there a way to replace the months in the formula a reference a 12 cell table with the months in it. the data being collected will all be formatted to use the same full month spelling.

 

[NYEXCEL1]

Iam getting an error with the formula. Does the {} around the months automatically be entered when i use control shift enter? i thought those indicators only showed up on the outside on the entire formula

 

[NYEXCEL1]

The error is a Divide by zero error. seem like it is not recognizing the months. to be clear the months are listed as mm/dd/yy format and then formatting to show the full month. For example, 6/1/17 shows as June. not sure if that is causing the issue

 

[NYEXCEL1]

Nevermind that was the issue...needed to be text not date. THANKS!!