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

Client	Team	Month	Revenue
Client A	Team A	June	500
Client A	Team B	July	100
Client A	Team A	July	500
Client A	Team B	August	500
Client A	Team A	August	100
Client A	Team B	September	500
Client A	Team A	September	100
Client A	Team B	October	500
Client A	Team A	October	100
Client B	Team B	January	800
Client B	Team A	January	200
Client B	Team B	February	800
Client B	Team A	February	200
Client B	Team B	March	800
Client B	Team A	March	200

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

`

 

[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]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Client

Team

Month

Revenue

June

July

August

September

October

January

February

March

2​

Client A

Team A

June

500​

Client A

500​

300​

300​

300​

300​

3​

Client A

Team B

July

100​

Client B

500​

500​

500​

4​

Client A

Team A

July

500​

5​

Client A

Team B

August

500​

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​

<tbody>
</tbody>

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!!