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

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

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

 

[kathleenfarber]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

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

Thanks! I think I updated my account info! It should be current now. Your solution didn't seem to work. :/

 

[Fluff]

I think I updated my account info!

Thanks for that.

Your solution didn't seem to work.

In what way?

 

[kathleenfarber]

Thanks for that.


In what way?

Nevermind it was MY own error!! You are fabulous. I've been terribly sick and just could NOT wrap my head around this.

 

[Fluff]

Glad it's working & thanks for the feedback.

 

[kathleenfarber]

Hey Fluff, I was wondering if you could further with this problem. Your solution is working great but now I need to take the total greater than 1 and instead of putting 10,000 I need to take the total of the "1s" and multiply it by the 10000. I've tried SUMIF and SUMPRODUCT but I'm still not able to figure it out.

 

[Fluff]

How about

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

 

[kathleenfarber]

It doesn't seem to be multiplying the sum of the 1's by the 10000. I tried that earlier before I bothered you. And I tried ...

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

 

[Fluff]

Works for me

+Fluff 1.xlsm
	D	E
5		60000
6	1
7	1
8	1
9
23
24	1
25	1
26	1
27
28
Main

Cell Formulas
Range	Formula
E5	E5	=10000*(COUNTIF(D5:D21,1)+COUNTIF(D24:D31,1))