Need formula to get Average value main sheet to summmary sheet using if, vlookup and average formula

[PK1988]

Hi All,
Need your help
I have a transport master file with all datas and I need to pull the average spend value for specific lanes in another sheet.
Here check point would be lane name to be matched in both the sheet and also its need to calculate the average value based on the status column "consider".

 

[Peter_SSs]

Welcome to the MrExcel board!

We have no idea how your workbook is set out or what your data is like so it is fairly unlikely that anybody will bee able to offer anything of much use.
Could you give us a small but representative set of sample data, include the expected results and explain again in relation to that?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[PK1988]

Hi, thanks for your message.
Below is the example: Need Formula to get Average value from the Raw data to summary sheet.
The formula should consider the location as well status " consider" and pull the average value.

Raw data

Vendor Name	Value	Status	Location
XXXX	431​	Consider	Apple
XXXX	443​	Consider	Apple
XXXX	888​	Not to consider	Apple
YYYY	320​	Consider	Orange
YYYY	319​	Consider	Apple
YYYY	315​	Consider	Orange
YYYY	890​	Consider	Orange
ZZZZ	270​	Consider	Lemon
ZZZZ	280​	Consider	Lemon

Summary sheet:

Location	Average Value	Comment
Apple		Need Average Value, status to be calculated for" Considered " alone
Orange
Lemon

 

[Peter_SSs]

Thanks for the sample data and further explanation. See if this would work for you.

PK1988.xlsm
	A	B	C	D
1	Vendor Name	Value	Status	Location
2	XXXX	431	Consider	Apple
3	XXXX	443	Consider	Apple
4	XXXX	888	Not to consider	Apple
5	YYYY	320	Consider	Orange
6	YYYY	319	Consider	Apple
7	YYYY	315	Consider	Orange
8	YYYY	890	Consider	Orange
9	ZZZZ	270	Consider	Lemon
10	ZZZZ	280	Consider	Lemon
Raw data

PK1988.xlsm
	A	B
1	Location	Average Value
2	Apple	397.6666667
3	Orange	508.3333333
4	Lemon	275
Summary

Cell Formulas
Range	Formula
B2:B4	B2	=AVERAGEIFS('Raw data'!B$2:B$10,'Raw data'!D$2:D$10,A2,'Raw data'!C$2:C$10,"Consider")