1. D

    vbscript .find ignore spaces

    I have a script that searches phone numbers Set rngFind = .Find(What:=Trim(txtLookup.Text), LookIn:=xlValues, LookAt:=xlPart) Some of the numbers have spaces and some are just numbers. eg in the database a phone number may show up as "0218888888" or "021 888 888" Is there a way so when my...
  2. K

    Need help with sumproduct weighed average

    I am trying to write a formula that will ignore 0 or blanks with the sumproduct function. My current formula is =SUMPRODUCT(F47:F53,G47:G53)/SUM(G47:G53) Now I need to ignore 0 or blanks in this formula to make it work correctly. Can someone help?
  3. A

    Ignore Hidden Cells Dunring Inconsistent Formula Calculation

    Hello All, I have a worksheet with a column of incrementing data. The first problem I have is that when I hide rows I want the formula to ignore the hidden cells and continue the sequence. The second problem is that my formula at the moment is inconsistent, For example: -- image removed --...
  4. O

    Rank duplicate numbers

    Hello again, guys I'm using the following formula to rank Risks according to the value in a cell. However, right now, the formula ignores duplicates. Let's say I have the same risk listed twice - which can happen. The formula will just ignore this and assign it the next available number. I was...
  5. R

    Line chart to ignore blank cell values with formulas

    I have a line chart based on the months of the year and performance goals with a formula. I want the line graph to ignore the cells with the formula, until that month actually has data collected. That way if I have data plotted for January through July, August through December wont be plotted...
  6. F

    IF and some blank cells

    Hi! First of all, I'm a total noob with Excel, no shame on that. Sorry, not sorry. So, I have some homework and I've been having problems with this: =IF(OR(AND(F2="Fever",G2="Cough"),F2="Bronquitis",F2="Ear Pain",F2="Sore throat"),"Cold",(there's more here but apparently there aren't issues...
  7. J

    Add calculated field based on alternating row values

    I have a data table that looks something like this (fake data obviously) that's been pulled from a text file and brought into Excel using the Power Query AddIn for Excel 2013. <tbody> Direction Email Outbound adam.zapple@xyz.com Inbound pepper.mintz@abcd.com Outbound...
  8. R


    Hi All, I'm using the RANK formula: =RANK(BL2,$BL$2:$BL$186,0) Some students do not have a grade so their cell is blank. How do I get the formula to ignore the blank cells? Many thanks, Russ
  9. K

    median in Pivot table question

    Saw the podcast on calculating median in a pivot table which works great ...except if you have empty/null values....is there a way to ignore the empty cells and calculate median on cells that have value in it?
  10. F

    Sumproduct Left function ignore blank cells

    How do I ignore the blank cells in this small range of five cells? I'm trying to sum the first three numbers in each cell. =SUMPRODUCT(--(A2:A6<>""),LEFT(A2:A6,3)) Excel 2016 (Windows) 64 bit A 2128/76 3 4118/76 5 6130/83 Sheet: BP
  11. G

    If false do nothing... but a little more difficult

    person1 A 66 person2 A 68 person3 A 70 person4 B 88 person5 B 89 person6 A 65 person7 C 90 person8 C 91 I WANT TO GET THE LOWEST SCORE FOR ALL PERSONS WITH AN 'A' NEXT TO THEIR SCORE? THIS IS THE IF/THEN I WROTE, BUT I DONT KNOW HOW TO IGNORE IF FALSE...
  12. L

    Condense a column (by using formula)

    I have 2 columns that is as follow: A B C D 12 0 6 11 0 4 23 3 3 21 4 8 11 0 10 6 17 0 17 0 28 8 10 0 I would like to have in column C, the order from small to large (of column A) based on column B when there is a value greater than 0...
  13. D

    SUM() Cell That Contains String&Integer

    Hi Guys, I've been searching for a day or two now to find exactly what i want, but i can't seem to find it anywhere. The problem i have there is a column with numbers and strings. 5 10D 51 Pax 40D 21 Now i want to add all the numbers with a "D" next to it, and ignore the integer cells and...
  14. X


    Apologies for previous post - I posted wrong thing altogether. I have the following sumproduct below and I'm trying to add to this to to ignore any blank cells if possible. thank you =SUMPRODUCT(--('3a6'!I2:I35>='3a6'!H2:H35))
  15. C

    How do I get this to ignore text?

    How would I get this formula to ignore the text in cell C4? I keep getting a #VALUE ! error. I want the formula to just use the numbers in the cell. =IF(SUM(C4*5+D4)*120%>50,SUM(C4*5)*120%,SUM(C4*5+D4)*120%) Thanks in advance.
  16. A

    Ignore result IF

    Hello all, I have 3 columns with YES NO in first 2 columns and status in the 3rd. I'm using COUNTIF yes in both first 2 columns but I want to ignore the row if the status is 'Out of Scope' OR 'Complete' =COUNTIF('B. General' !$E:$F, "yes") <tbody> E F G Answer 1 Answer 2 Status YES NO...
  17. D

    compare cell A to corresponding cell B and count if B > A but ignore cells with "<" sign

    I have the data set below and I need help to compare cell A to corresponding cell B and count if B > A but ignore cells with "<" sign: <tbody> A B 1 1.2 1 213 1 < 1.0 1 < 1.0 1 213 1 0.0014 1 0.0109 1 4 0.001 0.00045 0.003 0.1 0.003 2 0.005 0.074 0.003 0.08 0.003 <...
  18. D

    Ignoring Blank Cells

    Hi, I'm a bit rusty on the old Excel, and can't get my formula to ignore the blank cells as per my attached example. https://expirebox.com/download/c89685e8e2a4a9399fd5bfb474bf3e97.html Any help here would be greatly appreciated!! Thanks :)
  19. C

    VBA to copy/paste to another workbook but not if it's already in use(read only)

    Hi, I have several workbooks used by my team and I have a master copy as an overview. Currently, they update their individual sheets and then copy and paste it into the master copy. I want to set it up so that they can do it using a macro - that's straightforward enough I know, however as a...
  20. M

    How can I get a formula to ignore " " entries

    I have a columns with results = " " When I add up multiple columns (one containing " ") get a "#value" error. Is there a way to have the formula ignore " " ?

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...