Text and numbers
 

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

Your formula can return only a number.
but we can add text to the result with
="Late " &
SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)

However, the format of the number may not be correct so try
="Late " &
TEXT(SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100),
"hh:mm:mm")

Let us know if this works for you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"puiuluipui" wrote in message
...
Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and
numbers.

Can this be done? Thanks!

Text and numbers
 

It's working great!
Thanks!

"Jacob Skaria" a scris:

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

Hi, can you help me with one more problem? If the formula cant find anything
in a cell, can "on time" appear? If there is no entry the code to display a
message "on time".
Can this be done?
Thanks!

"Jacob Skaria" wrote:

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

You can handle that using ISNA() function

=IF(ISNA(<formula),"On time",<formula)

If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, can you help me with one more problem? If the formula cant find anything
in a cell, can "on time" appear? If there is no entry the code to display a
message "on time".
Can this be done?
Thanks!

"Jacob Skaria" wrote:

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

Hi, i can't make it work. I don't know how to brake this
=INDEX(DB!$C$2:$C$100;MATCH(1;(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=G3);0))
into two formulas to fit in your code =IF(ISNA(<formula),"On time",<formula)
Can you help me with this?
Thanks allot!

"Jacob Skaria" wrote:

You can handle that using ISNA() function

=IF(ISNA(<formula),"On time",<formula)

If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, can you help me with one more problem? If the formula cant find anything
in a cell, can "on time" appear? If there is no entry the code to display a
message "on time".
Can this be done?
Thanks!

"Jacob Skaria" wrote:

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!

Text and numbers
 

It's working!
I was tired last night. :)

Thanks!

"Jacob Skaria" a scris:

You can handle that using ISNA() function

=IF(ISNA(<formula),"On time",<formula)

If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, can you help me with one more problem? If the formula cant find anything
in a cell, can "on time" appear? If there is no entry the code to display a
message "on time".
Can this be done?
Thanks!

"Jacob Skaria" wrote:

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(DB!$D$2:$D$100,MATCH(1,(DB!$A$2:$A$100=B6)* (DB!$B$2:$B$100=E3),0))


If this post helps click Yes
---------------
Jacob Skaria


"puiuluipui" wrote:

Hi, i have this code.
=SUMPRODUCT(--(B6=DB!$A$2:$A$100),--(E3=DB!$B$2:$B$100),DB!$D$2:$D$100)
It's working great, but only if retrieve numbers. But "$D$2:$D$100" i have
"Late 00:04:56" (text and numbers) and i need the result to be
"Late 00:04:56".

So, ...the code shows only numbers. I need the code to show text and numbers.

Can this be done? Thanks!