sumif formula with conditions

[asyamonique]

Goog Day,
On cell C41 I want to put a formula which will calculate the total sum with the conditions below.

I've created a table from B6:D40
There will be numbers from B6:B40 and C6:D40 will have the letter info as X
My target is to get the total of those numbers.But if there is no info on column C as X
If there is info as X then that number will be reduce %5 from the original amount(if 100000 will consider 95000!)Many thanks.
[TABLE="width: 269"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]5%[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]COLUMN B[/TD]
[TD]COLUMN C[/TD]
[TD]COLUMN D[/TD]
[/TR]
[TR]
[TD]20,000[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]45,000[/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]50,000[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]100,000[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]300,000[/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]TOTAL:[/TD]
[TD="colspan: 2"]506,500[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlKey]

Try this

[B]=SUMPRODUCT((C$6:D$40="X")*B$6:B$40)[/B]

 

[asyamonique]

Thanks Alkey,
Thats good but only misssing is if there is an entry on column C total shold not same..


[TABLE="width: 277"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]120,000[/TD]
[TD] [/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]130,000[/TD]
[TD] [/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]200,000[/TD]
[TD] [/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]500,000[/TD]
[TD]x[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

So the total should be 925,000 not 950,000
My question was toreduce %5 from the related cell.

 

[Fluff]

How about
=SUMIF(D2:D6,"X",B2:B6)+(SUMIF(C2:C6,"X",B2:B6)*0.95)

 

[AlKey]

I think you math is wrong. To reduce the total amount of 950,000 by 5% will return 902,500
Here is a formula to get this result

[B]=SUMPRODUCT((C$12:D$40="X")*B$12:B$40)*(1-5%)[/B]