1. 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...
  2. 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"
  3. 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...
  4. 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.
  5. 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.
  6. 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...
  7. 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...
  8. 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!
  9. 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
  10. D

    Fill blanks macro

    Hello, I need to fill all blank cells , or = 0 in my selection with "1" Thank you
  11. 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...
  12. 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. In addition, while we are here - We have the following formula...
  13. 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))
  14. 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...
  15. 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...
  16. A

    Strip out first cell / Validation list / blanks

    Hi, I have a table of legal entities (L.E) and divisions in those entities, which I do not want to transform. I am aiming to produce a drop-down of relevant divisions for selection using data validation in another cell. Here is my dynamic range...
  17. D

    Transfer Multiple Rows Data in Single Row Excluding Blanks

    Hi All, I have the data of Product in Row A and its price in row B. Similarly Product in Row C and its price in row D. in Total having 3 list of products and prices. Maximum 10 entries user can enter in each row. I want all the products from those 3 rows in single row G and its price in H...
  18. D

    VBA to apply Grouping based on Leading Blanks in an Essbase Indented Retrieve

    The Hyperion Essbase Zoom-in displays members of a hierarchical dimension in a single column, with leading blanks in front of the member names. The number of leading blanks indicates the Level (generation) of the member. The top of the hierarchy has zero leading blanks, next level has five...
  19. matthewlouis

    INDEX and MATCH when there are blank cells in column

    I am trying to do an INDEX and MATCH where the INDEX column contains blanks. This formula works well until the column has blanks. Then I get #N/A {=INDEX(O6:O130,MATCH(TRUE,O6:O130<>0,0))} -- Column O returns #N/A =IFERROR(LARGE(P6:P1300,1),"") -- Column P also has blanks, returns #N/A...
  20. R

    Counta with If

    For whatever reason, I'm drawing blanks on this one. I'm currently using =COUNTA(tblCourses[Name]) to count the number of courses I have. I had to add a column to my table, tblCourses[State] and I intend to type "Inactive" if the course is no longer available. How could I modify the counta...

Watch MrExcel Video

This Week's Hot Topics

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
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 "".
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