Multiple If Average

[Silvermini63]

OK I need to find out the average of column “G” form person 1 (E1) if the type in column “J” is either “Modified Price” or “New Entry” so the average for Person 1 should be 13.62
Any help would be appreciated as I just cannot get my head around this one.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1					Person 1
2					Person 2
3	Customer	Name	Item	Description	Value	Cost	GM%	Userid	Concatenate	Type
4	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	25.05	Person 1	XXXXXXXXXXX	Expired
5	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	23.02	Person 1	XXXXXXXXXXX	Expired
6	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	29.70	Person 1	XXXXXXXXXXX	Expired
7	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	15.33	Person 1	XXXXXXXXXXX	New Entry
8	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	17.42	Person 1	XXXXXXXXXXX	Modified Price
9	XXXXXX	Cust2	XXXXX	XXXXXXX	xx.xx	xx.xx	8.12	Person 1	XXXXXXXXXXX	Modified Price
10	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	20.95	Person 2	XXXXXXXXXXX	Expired
11	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	38.36	Person 2	XXXXXXXXXXX	Expired
12	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	26.15	Person 2	XXXXXXXXXXX	Expired
13	XXXXXX	Cust4	XXXXX	XXXXXXX	xx.xx	xx.xx	12.15	Person 2	XXXXXXXXXXX	Modified Price
14	XXXXXX	Cust5	XXXXX	XXXXXXX	xx.xx	xx.xx	11.11	Person 2	XXXXXXXXXXX	New Entry
15	XXXXXX	Cust6	XXXXX	XXXXXXX	xx.xx	xx.xx	30.00	Person 2	XXXXXXXXXXX	Modified Price
610 (2)

Excel 2010

 

[asad]

Try this:

=SUMPRODUCT(--((J4:J15="New Entry")+(J4:J15="Modified Price")),--(H4:H15=E1),(G4:G15))/SUMPRODUCT(--((J4:J15="New Entry")+(J4:J15="Modified Price")),--(H4:H15=E1))

 

[wiegs187]

In column K you can put in the following formula in cell K4:

=IF(J4="Modified Price", G4,IF(J4="New Entry", G4,""))

Then copy the formula down to each row in column K. Then you can do an average of the range in column K for person 1. Excel won't count the blanks in the average, so if the cell in column J doesn't equal Modified Price or New Entry it will leave column K blank.

Probably not the best way, but it'll work.

 

[Silvermini63]

That looks spot on thank for that I will try it on the actual data and see how it goes.

 

[T. Valko]

OK I need to find out the average of column “G” form person 1 (E1) if the type in column “J” is either “Modified Price” or “New Entry” so the average for Person 1 should be 13.62
Any help would be appreciated as I just cannot get my head around this one.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1					Person 1
2					Person 2
3	Customer	Name	Item	Description	Value	Cost	GM%	Userid	Concatenate	Type
4	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	25.05	Person 1	XXXXXXXXXXX	Expired
5	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	23.02	Person 1	XXXXXXXXXXX	Expired
6	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	29.70	Person 1	XXXXXXXXXXX	Expired
7	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	15.33	Person 1	XXXXXXXXXXX	New Entry
8	XXXXXX	Cust1	XXXXX	XXXXXXX	xx.xx	xx.xx	17.42	Person 1	XXXXXXXXXXX	Modified Price
9	XXXXXX	Cust2	XXXXX	XXXXXXX	xx.xx	xx.xx	8.12	Person 1	XXXXXXXXXXX	Modified Price
10	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	20.95	Person 2	XXXXXXXXXXX	Expired
11	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	38.36	Person 2	XXXXXXXXXXX	Expired
12	XXXXXX	Cust3	XXXXX	XXXXXXX	xx.xx	xx.xx	26.15	Person 2	XXXXXXXXXXX	Expired
13	XXXXXX	Cust4	XXXXX	XXXXXXX	xx.xx	xx.xx	12.15	Person 2	XXXXXXXXXXX	Modified Price
14	XXXXXX	Cust5	XXXXX	XXXXXXX	xx.xx	xx.xx	11.11	Person 2	XXXXXXXXXXX	New Entry
15	XXXXXX	Cust6	XXXXX	XXXXXXX	xx.xx	xx.xx	30.00	Person 2	XXXXXXXXXXX	Modified Price
610 (2)

Excel 2010

Try this array formula**.

Criteria:

• E1 = Person 1
• F1 = Modified Price
• G1 = New Entry

Array entered**:

=AVERAGE(IF(H4:H15=E1,IF(ISNUMBER(MATCH(J4:J15,F1:G1,0)),G4:G15)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.