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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple criteria 3
sum text&numbers 10
find text and move it 2
tiny discrepancies in fixed-point numbers 3
pulling from one sheet to another 3
match 3
Text to Columns issue with Date & Time 3
Had to repost it!!! 1

Back
Top