Multiple if or sumproduct?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

Hope you can help me

I have a cell which needs imput...D24.....wiil be say 1,2,3 or

Whatever number is inputed in D24, cell G28 will show thw sum

If D24 = 1, D1
IfD24 = 2, D14:E1
If D24 = 3, D14:F1
If D24 = 4, D14:G1

If D24 is zero or greater than 4 is it possible to have a dialogue box appear with an error message

Thank

Alb
 
Found it!

{=SUMPRODUCT(IF($D$24=1,$D$14,IF($D$24=2,$D$14:$E$14,IF($D$24=3,$D$14:$F$14,IF($D$24=4,$D$14:$G$14,0)))))
the references are absolute, cos I copied them across from another workshee

Still not sure on the dialogue box, any ideas

Alby
 
error message? yes, try data validation (Data -> Validation -> whole
number -> between "0" & "4" -> set your error msg), or the vba route.

how about this one...

in D14:
=IF($D24>=COLUMN(D$24)-COLUMN($D$24)+1,44,0)
copy over, frankly as far as you want to.

in G28:
=SUM(D14:G14)
or however far your data goes

and don't forget the data validation in D24.

it's one way anyway.

hope this helps.
 
do the data validation for your dialog. works great for me. also, yo
can customize the message to be naughty or nice :-
 
Neither multiple If nor SumProduct...

=IF((D24>0)*(D24<=4),SUM(D14:INDEX(D14:G14,D24)),"Wrong Range")
 
Aladin,

I'm curious how your formula works. Just looking at this component:-

=SUM(D14:INDEX(D14:G14,D24))

The INDEX portion of the formula will return the nth value of the range
D14:G14 (where n = value in cell D24). The INDEX portion therefore returns a
*numeric* value. How does this fit in with the SUM part of the formula?
=SUM(D14:numeric value) should return an error!

Would appreciate an explanation. Thanks.

Now, putting this numeric value into the SUM formula should return an error,
but it doesn't!
 
Activate the formula cell. Go to the Formula Bar, select just the
D14:INDEX(D14:G14,D24) bit, and hit F9. The result that you see shows that
INDEX here functions as range specifier.
 
Back
Top