Modifying Sum Range in Sumif Formula

[andybason]

Hello

I have a table (extract below) and I am trying to do a sumif to find in-stock items and then deduct 10% from each price before summing the result.

I can do a sumif for the actual price (=SUMIF(Table1[stock],"in",Table1[price])) but I can't work out how to deduct 10% from each price first.

Can anyone help?

Thank you

| id | stock | price |
| -- | ----- | ----- |
| 1 | in | 6.8 |
| 2 | out | 5.9 |
| 3 | out | 18.5 |
| 4 | in | 6.2 |
| 5 | out | 18 |
| 6 | out | 5 |
| 7 | in | 7.8 |
| 8 | in | 6.6 |
| 9 | in | 9.6 |
| 10 | out | 3.7 |
| 11 | out | 12.5 |
| 12 | in | 4.6 |
| 13 | out | 14 |
| 14 | in | 17.5 |
| 15 | in | 10.5 |
| 16 | out | 13.5 |

 

[awoohaw]

try:

mr excel questions 24.xlsm
	A	B	C	D
1	id	stock	price
2	--	-----	-----
3	1	in	6.8	62.64
4	2	out	5.9
5	3	out	18.5
6	4	in	6.2
7	5	out	18
8	6	out	5
9	7	in	7.8
10	8	in	6.6
11	9	in	9.6
12	10	out	3.7
13	11	out	12.5
14	12	in	4.6
15	13	out	14
16	14	in	17.5
17	15	in	10.5
18	16	out	13.5
Sheet9

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT((C3:C18*(1-0.1))*(--(B3:B18="in")))

 

[Georgiboy]

Would taking 10% away from the sum you have give you the result needed or do you need it row by row?

Something like:

=SUMIF(Table1[stock],"in",Table1[price])*0.9

 

[Fluff]

Another option

=SUMPRODUCT((Table1[stock]="in")*(Table1[price]*0.9))