blanks

  1. J

    Need to sum the bottom 5 cells that have values....

    A B 100 1 100 2 500 3 400 4 100 5 200 6 7 200 8 100 9 100 10 I want to sum a maximum of the bottom five rows that have values. I want to skip the blank rows. So the results of the formula in B10 should be 100+100+200+200+100 = 700 I will use this formula in an adjacent...
  2. D

    Vstack ignore blanks

    Hi, Is there a better way of using vstack but ignoring the empty cells in each list that this; VSTACK(FILTER(Table2[A],Table2[A]<>""),FILTER(Table2[B],Table2[B]<>""),FILTER(Table2[C],Table2[C]<>"")) Richard.
  3. D

    Average time between dates *But ignore if blank in either cell

    Version: Excel Online I want an average of the time (in days) between two milestones for many different people (one person per row), but I want the formula to ignore that row if there isn't a date in BOTH date cells, since it throws off the average in a major way. Example "TABLE_1" EE Name...
  4. J

    Macro to Delete Blank Rows in Multiple Sheets, Not Whole Workbook

    I have been working on this macro for what seems like forever and I just can't get it to work. I have a data set and when employees are terminated their names are removed from the spreadsheet and I need any monthly sales data to also be deleted. Employee names all "feed" from a main...
  5. D

    Xlookup, If, etc formulas and the dreaded #Value! that comes with "" when pasting as values

    In xlookup you can define what your error response is ~~ =xlookup(a3,b:b,c:c,<whateveryouwantyourerrorresponsetobe>,option,option). Typically I use "" as my error response. However when you copy the cell and paste as value (the result being an error state equaling "") in another cell...
  6. J

    EXACT Function to ignore blank / empty cells

    Hi All, I have the following formula that works when I just look at a list of populated cells, but as soon as the list contains blank cells it then fails and returns 'In Progress'. Is there a way that I can ignore the blanks? My formula is...
  7. 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"
  8. 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...
  9. 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.
  10. K

    Excel Formula to show countif criteria from only visible cells.

    Hello, I need help with a formula to count if cell b2:b600 has the word "Alpha" when it has been filtered to display, Alpha, Bravo and Charlie. the formula i have only counts not blanks. =SUBTOTAL(3,'Sheet1t'!B2:B600) Thank you in advance.
  11. M

    VBA autofilter - filter blanks only

    Hi I've recorded a macro which filters blanks in a sheet. However, I need to modify it, so that it deletes the blanks from the range in the sheet regardless of the number of rows. Does anyone know how to modify the code below, please? Also, is there a good tutorial on VBA autofilters that...
  12. F

    count blanks and non blanks same formula

    Needing a formula to count non blanks in 1st column and blanks in the 2nd column. So the example it would count 2. the text is giving me issues trying to do a COUNTIFS formula and using COUNTA formula. I've searched and tested but nothing is working for me. appreciate any help in advance...
  13. J

    Average last 5 non-blank cells

    <tbody> 1 1 0 1 1 0 </tbody> Hello there, My goal is to get the 60.00% but what formula would I use to exclude blanks and only consider the bottom 5 values? Thanks!
  14. O

    Using MIN function in column with blanks

    I have a scoring sheet that lists scores in a column but never know how many names will be entered and want to find the minimum score in a column with several blanks above the formula
  15. D

    Fill blanks macro

    Hello, I need to fill all blank cells , or = 0 in my selection with "1" Thank you
  16. N

    Conditional Formatting for highlighting after today's date.

    I am looking to highlight any dates that passed, however, when I use the built in format it highlights the blanks as well. When I used if($n6<=today(),"true","") it does the same thing. My goal is to just highlight any dates that are today's date and past, without the blanks being highlighted as...
  17. P

    Formula Help: Counting Blanks

    Hi I am struggling with writing a formula to achieve the following: We want to count how many times there are two or more consecutive blanks. Below is a snapshot of what the data looks like. https://imgur.com/a/SP7Ojyz In addition, while we are here - We have the following formula...
  18. G

    Ignoring blanks in a sumproduct formula

    HI, I have this formula but it keeps returning #VALUE because there are some blank cells in AW40:AW53. How do i get it to ignore the blanks? Thanks =SUMPRODUCT((C40:C53=C6)*(AW40:AW53)*(G40:G53))
  19. TheRogue

    Limited Data Validation Choices

    <tbody> THINGS Red Blue Yellow </tbody> I have the above table that I am using as a Data Validation List. The Data in the Table is dynamic & does not always have the same # of items in it. For the purpose of Data Validation, I have given Table1[THINGS] the Named Range of...
  20. 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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top