Hi there,
I have attached a spreadsheet to help you see what I am referrign to:
I am trying to get column J to be blank until there is data in cells A, B, C or H.
Cells A, B, C and H contain IF(ISBLANK) formulas, but so does column J, so I am wondering if it is not *truly* blank if there is a formula there and that's why it is populating "Action" all the way down the column?
Any help would be greatly appreciated!
Thanks!
A Null String ("") is a 0 Length Text string - ISBLANK (like COUNTA) treats Null Strings as non-blanks whereas COUNTBLANK for ex. does not.
Regards J
I confess I've not reviewed your formula in Col K but I suspect you can streamline - what I would say is:
Please Login or Register to view this content.
1. avoid using SUMPRODUCT with entire column references as the function is inefficient
2. use SUMIFS in preference to SUMPRODUCT if viable (as is seemingly the case here)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I tried the formula you suggested and it says I've entered too many arguments?
The sumifs does not work for my column K formula. it says too few arguments have been entered.
I'm not sure if I should head the macro-writing route or not. IN which case I'd need further help, I'm guessing.
Oops - sorry about that - COUNTBLANK accepts only contiguous ranges
If you want people to review Col K please indicate as such and outline expected results etc...Please Login or Register to view this content.
Regards SUMIFS - from looking at your SUMPRODUCT just now you should be using COUNTIFS...
you can replace your three ISBLANK tests within the AND with A2&B2&C2=""
Last edited by DonkeyOte; 03-23-2011 at 09:21 AM.
Without having looked at set up of criteria sheet
should prove a lot more efficient than the pre-existing approach but I am sure with more time & thought it could be streamlined further.Please Login or Register to view this content.
(COUNTIFS/SUMIFS are both sufficiently efficient that using entire column references is no big deal - it's the quantity of these formulae being executed that will affect calculation time)
that formula is now giving me a cell value of 1?
Basically what I need is for my techs to just enter the sample site and bacteria counts and have the formulas do the rest. I have made tables on the criteria tab that will populate columns A, B and C and then I have a formula that calculates the total amount for the bacteria if it is an air sample. And I have gotten the formulas to work for that purpose, but it populates "Action" even when the cells are blank for whatever reason. So that's where I need your expertise with the ISBLANK function.
I'm new to the formula business and I'd appreciate any troubleshooting you can provide.
thanks!
sorry that previous response was referring to the formula in your post #4.
and i tried the other formula you provide (in post #5) and i get the cell to populate "Alert" (when all other cells are blank, cell K should also be blank.
thanks!
Last edited by camdameron; 03-23-2011 at 09:30 AM.
Whereas it should be.... ?Originally Posted by camdameron
I adapted the formula you had in place in Col J which tested A & H - if you want to add additional cells to the check (per your OP - ie B & C) then:
the above would leave J blank until such time as A,B,C & H were neither blank nor null stringsPlease Login or Register to view this content.
However, it should be noted that for J2 the result would still be 1 given all referenced cells contain data.
Please post a further sample file to illustrate [include also expected results] as I am not able to replicate the above I'm afraid.
To confirm the formula for column K... presently if A:C are ALL blank/null strings then the result is also null string (rather than Alert) - this apes the logic of your original.
If you need to add H to this append the concatenation accordingly - A2&B2&C2&H2=""
So, what I'm trying to accomplish with this sheet is to have a tech enter in a sample site in column E, which will populate the cells in columns A, B and C (based on the information I have already established in columns A, B and C on the criteria tab). Columns A, B and C should be empty until a value is entered into column E. Column J will also be empty until a value is entered into column H, which will then calculate the correct total CFU based on whether it is an air or surface sample. Column K should be empty until there are values in A, B, C and H which it will then calculate whether it is an alert or action based on the criteria I have already established on the criteria tab.
The problem I am running in to is that when the cells in A, B, C and H are all blank, the formula still populates "Action" in column K, when I need it to be empty.
If you note that K2 through K11 all have properly populated cells (blank when it does not meet the criteria and Alert or Action when it does) but for the cells where there is no data entered in cells A-J, it still populates "action" in the K cell.
The expected results are that an entire row with no data (besides the formulas) should have a blank column K (cell K13 is what it should look like (i've removed the formula to show what it should look like).
Does this help?
The formulae / suggested amendments provided previously should suffice - for clarity I will repost:
Please Login or Register to view this content.
i'm a bonehead.
IT WORKED!!!!
thank you so much for your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks