Array help under certain condition return the values

[Vochey88]

Hi all,
Just joined this community as would like to improve my excel skills.
I want to make a shopping list from a pivot table of ingredients. Now i've put a drop down to the right of the quantity to put if it's needed or not. Now i want to add the ingredients that are needed to the shopping list starting G7 etc so anything that says "yes" in the D column. I've tried watching youtube videos of array etc but can't really get my head round it could someone point me in the right direction obviously i don't want to add any of the No parts in the list.

I hope that makes sense! Thanks in advance.

 

[etaf]

you could use FILTER()
=FILTER(B3:B37,D3:D37="YES")

doubt.xlsx
	H	I	J	K	L
1
2
3	Row Labels	Sum of WDV RATE
4	HAL3.5	100.7833333	yes		HAL3.5
5	HBOA1	7.334974867	no		HBOA4
6	HBOA4	39.87802378	yes		HCL1.30
7	HCL0.60	3.657814114	no		HCSS3.00
8	HCL1.30	5.14676434	yes		HFAB3.60
9	HCL1.80	6.940103664	no		HFMS3.15
10	HCSS3.00	22.42447662	yes		HMAT1027
11	HCW1000	-414.9915385	no		HMTBE
12	HFAB3.60	51.7885974	yes		HR1.5BB1.83
13	HFAB4.20	52.97228571	no		HRASIEG3.07
14	HFMS3.15	130.2968571	yes		HRL1.22
15	HLBOA0.50	1.208828912	no		HRLG6.4
16	HMAT1027	13.58111111	yes		HSWBJ
17	HMFBU	835.73	no		HTB3.30H20
18	HMTBE	17.64875	yes		HTFC
19	HPR5	26.96449553	no		HTPGSB0.50
20	HR1.5BB1.83	79.0851	yes		HTSW
21	HRASIEG2.57	505.648	no		HTUB5
22	HRASIEG3.07	505.648	yes
23	HRJWB	31.6536079	no
24	HRL1.22	52.85805017	yes
25	HRL1.57	49.71520161	no
26	HRLG6.4	502.8447	yes
27	HRS4	173.1596533	no
28	HSWBJ	1.955555556	yes
29	HTALC	0.951060114	no
30	HTB3.30H20	29.56763158	yes
31	HTBPAL	215.8323529	no
32	HTFC	0.204797688	yes
33	HTLB	17.94518519	no
34	HTPGSB0.50	2.50647619	yes
35	HTPGSB2	10.37414368	no
36	HTSW	2.248865946	yes
37	HTUB2	5.426882688	no
38	HTUB5	32.83466846	yes
39	Grand Total	3121.824811	no
BBBBB

Cell Formulas
Range	Formula
L4:L21	L4	=FILTER(H4:H38,J4:J38="yes")
Dynamic array formulas.

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone