1. J

    Formula for counting

    I am trying to achieve the results in D3. I'm in need of a formula that will count unique names, ignoring the blanks and duplicates. Also ignoring the word "na" and "ns"
  2. J

    counting, ignoring multiple words and blanks

    I need formulas that will count unique occurrences, certain words while Ignoring blank cells and duplicates. I am trying to achieve the results in cells D8 and D9. In this case, I need to count occurrences that starts with W81UTH excluding duplicates and blank cells. I also need to count ALL...
  3. J

    Formula for Unique Occurence using multiple columns

    I am trying to achieved the desired results on E4. I'm looking for a formula that will count a unique occurrence specific to a date, ignoring duplicates and blanks. In this case, 19NOV19 has 2 trucks. 20NOV19 has 3 trucks. 21NOV19 has 2 trucks.
  4. J

    Formula help, counting unique occurences.

    I am looking for a formula that will count unique occurrences that start with W81UTH, ignoring duplicates and blank cells. Also looking for a formula that will count unique occurrences that does NOT start with W81UTH, ignoring duplicates and blank cells. I want to achieve the desired results...
  5. D

    Remove characters in formula

    I need to run this formula but need it to ignore the text that is in cell W12. Formula is =sum(w12*t12)/100 Cell w12 reads as ‘15 kestrel’ So I want to ignore everything but the 15, I have other cells with different text so it has to ignore generic text.
  6. D

    How to ignore #DIV/0! error

    Array formula is {=AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18)))}. Want to ignore the #DIV/0! error and use the cell in column H corresponding to the I column having the #DIV/0! error. TIA
  7. C

    Conditional SUMPRODUCT to also ignore blanks

    Hi, I have the following code which calculates a weighted average: =SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203) It works very well except that I would like it to ignore blank cells in the range $AB$2:$AB$1203 I tried to change it to the...
  8. sharky12345

    Highlight cells less than 100% but ignore blanks

    I'm trying to use the conditional formatting option to highlight cells where the value is less than 100%, (the cell values are percentages), but I need to ignore any cells that are blank. Can anyone help me please?
  9. most

    How to solve a issue with three criterias

    Hi, I would like some input/pointers on how to solve this, I'm not sure where to begin... I want to write Active, Not active or Ignore in column E based on three criteria. As you can see in the table, a person can occur 2-3 times, even 6 times in my orginal data. Data is sorted on column A. If...
  10. R

    IF Function ignoring NA

    Hello guys, I am currently processing a huge dataset. My question is that how can I ignore NA when I want to run and repeat this function in excel. =IF(AND(RankLN!D45>0.3,RankLN!D45<=0.7),"M",IF(RankLN!D45>0.7,"W","L")) In detail, for example, I have a column, we say column D. In this column...
  11. R

    If function ignoring NA

    Hello guys, I have a column of numbers and some NA. I am creating dummy variables, for example, IF(A2>0.7,1,0) and repeat this function into the entire column. However, I have NA in this column. Therefore, how can I ignore NA and set the output of NA as 'NO'. Thank you so much.
  12. C

    Data Validation - Ignore Blanks Not Working?

    Good evening, I'm trying to create a drop-down that is essentially generated by an array formula. The range is 15 cells, but the list often has less than 15 values. Is there a way to get the drop-down menu to not have a bunch of blank white space at the bottom with a scrollbar for lists that...
  13. A

    VBA to Highlight entire row with conditions

    Hi, I am looking for VBA to highlight the entire rows which satisfy any one of the following conditions for active sheet. 1. IF Column J blank and Column R has some data then Highlight(vbyellow) 2. If Column P and Column R both are ‘0’ and column J is ‘A’ then highlight (vbyellow)...
  14. D

    MS Excel Spell Check not working properly

    I am using MS Excel 2016 and the spell check will not allow me to select 'Add to Dictionary', 'Ignore Once', 'Ignore All', etc. The buttons are live, yet no response when I click them. The only button that seems to function is the cancel button. Any help on this is appreciated. Thank you.
  15. D

    If Cell Blank ignore, otherwise Concatenate

    Hi Guys, Wondering if you could assist me with this simple formula - I'm trying to combine, "column E" with "column D" with a "." in between, I did the first one as an example - "BQ6924-001" add the "." and then the "3.5UK" so we get "BQ6924-001.3.5UK" I want to ignore the blanks though in...
  16. P

    Count but ignore duplicate

    Guys I have this below table where I want to count the number of occurence of each individual but ignore the duplicate instances Dates. Names 1. X 1. G 2. X 2. X 3. G 4. X 5. G 5. G Please help me, it's urgent
  17. S

    Ignore blanks formula

    Hello, Please may someone help me. I have a number of tabs in my spreadsheet, some have datapopulated, others don’t. I have an overview tab that brings everything together. I am simply using the formula: =tab!cell However this is bring in a ‘0’ when there is no data (thecell is blank), does...
  18. P

    Index match ignoring zero values (help)

    How can I make this formula ignore blanks and zero values? =INDEX(O9:BV9,MATCH(MIN(O10:BV10),O10:BV10,0)) Thanks, Pujo
  19. S

    Ignore Blank Cells in Drop Down List

    Hi Following some very useful help yesterday I was able to plough on with my project. I am now on my final UserForm and have a combobox2 which is coded: Private Sub ComboBox2_DropButt*******() With ComboBox2 ComboBox2.List = Worksheets("BankRec").Range("A1:A175").Value End With End Sub This...
  20. W

    Increase count by 1 but ignore multiple characters to the left

    I've seen a few things around for something similar, but I'm starting to wonder if this is possible... I formula that finds the last filled cell in range "A". That cell contains the following text "DEV-19-1". I need to increment the last digit (will eventually be 2 digits), but ignore the...

Some videos you may like

This Week's Hot Topics