Hello I have the following forumla:
However if E56 is blank I want the cell to remain clear.Code:=IF(ISNA(COUNTIF('SUPP LEG FLAGS'!A:B,E56)),"",COUNTIF('SUPP LEG FLAGS'!A:B,E56))
How would I instigate this?
Many Thanks
Andy
Last edited by Andy2010; 01-20-2010 at 05:06 AM. Reason: Solved
Try:
=IF(E56="","",IF(ISNA(COUNTIF('SUPP LEG FLAGS'!A:B,E56)),"",COUNTIF('SUPP LEG FLAGS'!A:B,E56)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
If as implied you're using XL2007 you might want to use:
IFERROR obviously not backwards compatible with earlier versions though... (and we're assuming #N/A! is your only underlying error...)Code:=IF(E56="","",IFERROR(COUNTIF('SUPP LEG FLAGS'!A:B,E56),""))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thats Fantastic guys, both work, Think ill have to use the non IFERROR one as people using 2003 will need it to work...
Whilst im here....to pick brains another one would be:
This code is working however in column G of 'SUPP LEG VALUE'! there is a date.Code:=IF(ISNA(COUNTIF('SUPP LEG VALUE'!B:B,B5)),"",COUNTIF('SUPP LEG VALUE'!B:B,B5))
I would only like it to display the data if the date is after the 01/02/2009. How would i do this?
Many Thanks
Andy
Try
=IF(ISNA(COUNTIF('SUPP LEG VALUE'!B:B,">=01/02/2009")),"",COUNTIF('SUPP LEG VALUE'!B:B,">=01/02/2009"))
That;s for one conditon...
Sorry... are there now 2 conditions.. one for column B and one for column G?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Originally Posted by NBVC
Yes there is 2 conditions unfortunatly. The B5 Value must be present in the SUPP LEG VALUE TABLE, but also in the SUPP LEG VALUE TABLE there is a column "G" which contains the date.
Hope this makes sense?
Thanks for your help!
Andy
You will need SUMPRODUCT then.. which does not allow full column references in PreXL2007
Adjust ranges to suit...Code:=IF(ISNA(COUNTIF('SUPP LEG VALUE'!$B$1:$B$100,B5)),"",SUMPRODUCT(--('SUPP LEG VALUE'!$B$1:$B$100=B5),--('SUPP LEG VALUE'!$G$1:$G$100>="01/02/2009"+0)))
.. this also assumes you are checking for #N/A error only in column B
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks that has now workedCheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks