Excel Formula

[Dee]

Hi

I'm hoping someone can help me with this formula I need. I need to look up
where cell D1 falls between columns A and B and give me the answer in column
C in column E..is this even possible? Thanks for your help

A B C D E

1 10 14.9 200 16 300
2 15 19.9 300 14.9 200
3 20 24.9 400 22 400

 

[Eduardo]

Hi,

=SUMPRODUCT(--(D2>=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9)

copy formula down

 

[Dee]

Hi Eduardo

thanks for you help....however this formula is giving me a "0" answer??

 

[Eduardo]

Hi,
I type your example and I tested the formula, are your values being typed or
imported from another source?, check if you don't have blank spaces or do the
exercise I did manually copying the numbers

 

[Dee]

Hi Eduardo

the numbers are manually typed. You put this formula in cells E1, E2 and E3
to get the 300, 200 and 400 answers

 

[Eduardo]

Hi,
I started in cell E2 if you start in E1 just change the range as follow

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)

 

[Dee]

Hi Eduardo

Its too bad I can't upload the spreadsheet but this is a copy/paste of what
I have...where the "0" is I have the formula as you have....but it should
come back as 200, 300, 400 just like column c


10 14.9 200 16 0
15 19.9 300 14.9 0
20 24.9 400 22 0

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)

 

[Joe User]

Its too bad I can't upload the spreadsheet

You could use Google Docs to upload and share the Excel file.


Dee wrote previouslly:

this formula is giving me a "0" answer?? [....]
=SUMPRODUCT(--(D2>=$A$2:$A$9),
--(D2<=$B$2:$B$9),$C$2:$C$9)

I wonder if some of the "numbers" are actually text. In comparisons of the
form text<=number, the result is always FALSE. And if any of C2:C29 is text,
SUMPRODUCT treats them as zero.

First, look at the format of the cells. If it was Text before you entered
the numbers, they will be entered as text. If the cell has a numeric format
(General, Number, etc), the content might still be text. Use a formula like
=ISNUMBER(A2) to be sure.


----- original message -----

 

[Dee]

Thanks Joe

That's what is was...the text/number problem...thank you so much

Its too bad I can't upload the spreadsheet

You could use Google Docs to upload and share the Excel file.


Dee wrote previouslly:

this formula is giving me a "0" answer?? [....]
=SUMPRODUCT(--(D2>=$A$2:$A$9),
--(D2<=$B$2:$B$9),$C$2:$C$9)

I wonder if some of the "numbers" are actually text. In comparisons of the
form text<=number, the result is always FALSE. And if any of C2:C29 is text,
SUMPRODUCT treats them as zero.

First, look at the format of the cells. If it was Text before you entered
the numbers, they will be entered as text. If the cell has a numeric format
(General, Number, etc), the content might still be text. Use a formula like
=ISNUMBER(A2) to be sure.


----- original message -----

Hi Eduardo

Its too bad I can't upload the spreadsheet but this is a copy/paste of what
I have...where the "0" is I have the formula as you have....but it should
come back as 200, 300, 400 just like column c


10 14.9 200 16 0
15 19.9 300 14.9 0
20 24.9 400 22 0

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)