Not sure which formula to use on this.

[west5405]

In cell S7:S15 I would like to count every quantity of price no matter what item it is.

Please checkout sample at below

G-testing1.xlsx

1drv.ms

 

[jasonb75]

Many forum users are not able to download files from weblinks due to security restrictions, see the link below for details of how to attach your sheet directly to your thread.

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

 

[west5405]

In cell S7:S15 I would like to count every quantity of price no matter what item it is

G-testing1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Item	Location	Price1-1	Price2-1	Location2	Price1-2	Price2-2	Location3	Price1-3	Price2-3	SUM Qty1	SUM Qty2	Sum Price1	Sum Price2	SUM Total
2	Apple	South	2		East	3	2	North	3	4	8	6	120	120	240		Item Count	0
3	Orange	East	4	5	North	2	5	West	8	10	14	20	980	1600	2580		Total Quantity	221
4	Avocado	West	8	2	North	5	5	West	1	8	14	15	840	900	1740		$$	$ 10,115.00
5	Banana	North	7	5	West	5	6	South	5	6	17	17	1530	1530	3060
6	Mango	North	4	8	West	1	7	East	5	5	10	20	400	1000	1400		Price	Quantity
7	Grape	West	5	9	South	5	1	North	1	1	11	11	220	275	495		15
8	Watermelon	East	5	2	South	2	5		6	4	13	11	#N/A	#N/A	N/A		20
9	BlackBerry		7	3		6	9		3	6	16	18	240	360	600		25
10																	40
11																	50
12																	70
13																	80
14																	60
15																	90
Main

Cell Formulas
Range	Formula
M2:M9	M2	=INDEX(PriceList[Price1],MATCH([@Item],PriceList[Item],0))*[@[SUM Qty1]]
N2:N9	N2	=INDEX(PriceList[Price2],MATCH([@Item],PriceList[Item],0))*[@[SUM Qty2]]
R2	R2	=COUNT(A2:A25)
R3	R3	=SUM(Main_090719[[SUM Qty1]:[SUM Qty2]])
R4	R4	=SUM(O:O)
L2:L12	L2	=[@[Price2-1]]+[@[Price2-2]]+[@[Price2-3]]
O2:O12	O2	=IFNA([@[Sum Price1]]+[@[Sum Price2]],"N/A")
K2:K15	K2	=[@[Price1-1]]+[@[Price1-2]]+[@[Price1-3]]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A10:A1048576,A1:A8	Cell Value	duplicates	text	NO

Below is table from other sheet

G-testing1.xlsx
	A	B	C
1	Item	Price1	Price2
2	Apple	15	20
3	Grape	20	25
4	Mango	40	50
5	Orange	70	80
6	Avocado	60	60
7	Banana	90	90
8	BlackBerry	15	20
9	Cherries	20	25
10
11
PriceList

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

 

[jasonb75]

Change the formulas in columns M and N so that they only contain the INDEX part of the formula to give a unit price, e.g. =INDEX(PriceList[Price1],MATCH([@Item],PriceList[Item],0)) and change the column header to Unit Price1 (repeat for each price column in the price list),then add the multiplier, *[@[SUM Qty1]] in another column, e.g. [@[Unit Price1]]*[@[SUM Qty1]]

Then you could use a simple SUMIF formula in column R, e.g. =SUMIF(Main_090719[Unit Price1],[@[Price]],Main_090719[SUM Qty1])
As you have multiple columns, you will need a sumif formula for each of them, then add the results together.

 

[west5405]

Change the formulas in columns M and N so that they only contain the INDEX part of the formula to give a unit price, e.g. =INDEX(PriceList[Price1],MATCH([@Item],PriceList[Item],0)) and change the column header to Unit Price1 (repeat for each price column in the price list),then add the multiplier, *[@[SUM Qty1]] in another column, e.g. [@[Unit Price1]]*[@[SUM Qty1]]

Then you could use a simple SUMIF formula in column R, e.g. =SUMIF(Main_090719[Unit Price1],[@[Price]],Main_090719[SUM Qty1])
As you have multiple columns, you will need a sumif formula for each of them, then add the results together.

OMG it work very well TQ so much.
If still have some question to ask.

G-testing1.xlsx
	A	B
1	101
2	102
3	103
4	T101
5	T102
6
7	3
8
Sheet1

How to make it count with letter & the return =5

 

[jasonb75]

=COUNTA(A1:A6) will count any cells that are not empty.

Note that this includes any blank cells that contain formulas.