IF AND plus a SUM

[kathleenfarber]

I am trying to have a cell look at D5 through D21 (skip 22 and 23) then D24 through D31. If those cells have a 1 in them and the total of all the D cells is greater then 2, I want the cell to show a $10,000 discount.

Any help appreciated. I've been looking at this since yesterday and I just can't seem to figure it out.
~kat

 

[kathleenfarber]

Very odd. I copied it again and it worked. First time I hand typed it.

Is there a way to still keep the greater than? I tried adding it back and it stopped working again. Swear this is driving me nuts this week.

 

[Fluff]

Is there a way to still keep the greater than?

Not if you want to multiply 10000 by the total number of 1s

 

[kathleenfarber]

Ugh. I don't know how to do this then. Thanks for the assist.

 

[Fluff]

What are you trying to do?

 

[kathleenfarber]

Cost	Selection	Subtotal
$30,000​	1​	$30,000
$45,000​		$0
$40,000​		$0
$30,000​	1​	$30,000
$40,000​		$0
$30,000​		$0
$50,000​	​	$0
DISCOUNT: (2 or more)	[discount here]

For each selection, there is a $10,000 discount applied for 2 or more. No discount for 1. So in the above example, there would be a $10,000 discount but if I chose 2 more areas then it would be a $30,000 discount.

 

[Fluff]

In that case how about

=10000*(COUNTIF(D5:D21,1)+COUNTIF(D24:D31,1)-1)

 

[kathleenfarber]

That seemed to have work. I'll fiddle around with the rest as it shows -10000 when nothing is selected and it's not formatting to the currency. But, thank you so much for the help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kathleenfarber]

Just for a final note! I think I have the rest solved. Seems to be working.

=MAX(0,(COUNTIF(D5:D21,1)+COUNTIF(D24:D31,1)-1)*10000)