Text and numbers

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

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!
 
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
 
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
 
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!
 
You can handle that using ISNA() function

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

If this post helps click Yes
 
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!
 
Back
Top