Counting text in fields, missing out blanks

[StoneColdSaysSo]

Hi

First post here and a total newbie so please be patient

I had a COUNTA formula set up which was counting fields which had text in it and giving me the total number of fields which contained text.

However I have recently amended the cells where the COUNTA was looking at. They now, rather than contain straight text, contain a VLOOKUP function.

I have this function set up so that if it returns what I am after then it will show the VLOOKUP data, however if it doesn't return anything rather than returning the N/A error it gives me a blank cell.

However the difficulty I now have is that my COUNTA function recognises that, despite it appearing as a blank cell, that there is in fact a formula in there and as such counts this as a populated cell when in fact I need it to show as a non populated one.

The dta that can be in the cells that the VLOOKUP is populating can be vast so I was wondering what the easiest way to go about this is so that the function I use ignores the 'blank' cells and only counts the ones that return something. Would it be a different function instead of COUNTA for example, like DCOUNTA or COUNTIF?

The VLOOKUP I am using is this:

=IF(ISNA(VLOOKUP("X",Y'!B4:G423,2,FALSE)),"",VLOOKUP("X",'Y'!B4:G423,2,FALSE))

Any help would be greatly appreciated.

Many Thanks

[DonkeyOte]

It's not clear what the VLOOKUP is returning but if it's a text string then:

=COUNTIF(<range>,"?*")

If the VLOOKUP is returning a Number

=COUNT(<range>)

If it's a mix:

=ROWS(<range>)-COUNTBLANK(<range>)

The fact is that a formula Null ("") is treated as non-blank by COUNTA/ISBLANK etc but not so by COUNTBLANK
The COUNTIF wildcard approach makes use of the fact that a Null is a 0 length text string

[StoneColdSaysSo]

It's not clear what the VLOOKUP is returning but if it's a text string then:

=COUNTIF(<range>,"?*")

If the VLOOKUP is returning a Number

=COUNT(<range>)

If it's a mix:

=ROWS(<range>)-COUNTBLANK(<range>)

The fact is that a formula Null ("") is treated as non-blank by COUNTA/ISBLANK etc but not so by COUNTBLANK
The COUNTIF wildcard approach makes use of the fact that a Null is a 0 length text string

Hi, thanks for the reply. The VLOOKUP returns a persons name.

So would it be possible to do a COUNTIF and make it only count it if returns a value longer than 2 characters or something (as a presume that the "") would return as either a 0 or 1 character response?

[DonkeyOte]

The COUNTIF provided will only count non-null text strings (as mentioned previously a Null is 0 length)

see XL Help for more info. on COUNTIF with demo. of wildcards etc...

[StoneColdSaysSo]

So what I am thinking then is something along the lines of:

=17-(COUNTIF(B4:B20,"<1>"))

With 17 being the maximum number of people's names it can return, so it should give me the number I am after this way shouldn't it? I think.......:D

[DonkeyOte]

Why not try the suggestion...

=COUNTIF(B4:B20,"?*")

the above is per post 2 only <range> is replaced by the VLOOKUP formulae range (previously unknown)

[StoneColdSaysSo]

Why not try the suggestion...

=COUNTIF(B4:B20,"?*")

the above is per post 2 only <range> is replaced by the VLOOKUP formulae range (previously unknown)

Just put that it and works fine thanks.

Next question (sorry)

When replicating the VLOOKUP forumla into the next area it pulls wrong information.

As mentioned before the original VLOOKUP was:

=IF(ISNA(VLOOKUP("X",Y'!B4:G423,2,FALSE)),"",VLOOKUP("X",'Y'!B4:G423,2,FALSE))

I am now changing to the X value to X1, however what happens is the first 11 results (theer were 11 positive results from the first one) from the formula all give the same return (again this is a persons name). Is there something that I need to add to get it to only select the correct data rather than repeating the first person under X1 11 times?

Its probably something very easy, apologies as I am a relative newbie with this function.

Thanks for your help so far

[DonkeyOte]

VLOOKUP in this context will only ever return the value associated with the first exact match of criteria it finds.

If you're looking to return multiple results for the same criteria value you will need to use a different approach I'm afraid.

I would suggest to avoid confusion you post a sample file that reflects both set-up and requirements (ie desired results given sample data).
Change values that are confidential but ensure the file uses the same ranges / data types etc such that it is truly representative of your real file.

[StoneColdSaysSo]

VLOOKUP in this context will only ever return the value associated with the first exact match of criteria it finds.

If you're looking to return multiple results for the same criteria value you will need to use a different approach I'm afraid.

I would suggest to avoid confusion you post a sample file that reflects both set-up and requirements (ie desired results given sample data).
Change values that are confidential but ensure the file uses the same ranges / data types etc such that it is truly representative of your real file.

The first 11 all pulled through correct and with the correct data (multiple results) but the second VLOOKUP string didnt.

I'll try show you some examples see if there is anything can be done. Again thanks for your pateience.

I've attached a very basic version of the document. Sheet one is where I take the data from . Sheet 2 is what I am currently experiencing. Sheet 3 is what I would like to get to.

EDIT - The ranges are not the same, however the data types are.

[DonkeyOte]

Based on the sample I would suggest the below:

Please Login or Register  to view this content.

The above is all based on premise that per the sample Sheet1 employees are grouped together by Manager.

[StoneColdSaysSo]

Based on the sample I would suggest the below:

Please Login or Register  to view this content.

The above is all based on premise that per the sample Sheet1 employees are grouped together by Manager.

I tried putting that in to the sample document and it said something about a circular reference. I'll have another shot in a bit.

Thanks for your help so far

[DonkeyOte]

You will get a circular reference warning if you adjust B19/E19 after rows 2:18 - hence row 19 changes listed first in suggested order of alterations.

Please also note there is no need to quote my posts in your reply - this simply clutters your thread