I don't see that working for 2 reasons.

1) ">D1" is just taken as that string of 3 chars. Don't need cell D1 at all; just use ">4" that does work.

2) Anyway this just counts up how many cells are not 4, not how many are 4.

It looks for a number in the range matching the number specified in D1.

If you enter a '4' in D1, it will look for the number 4, return error for none, or more than 1, or return the corresponding text in the B column. You could add in another cell for text match TRUE/FALSE, but it gets silly if there's the likelihood of duplication.

Hi Guys,

I wont be back at the computer with the document on until tomorrow so can't try any of the above yet.

Basically I have a table where I am entering data and to the right of that I have a matrix setup and was trying to get the cells in the matrix to return True or False from the data I have entered in to he table. I was then going to set a conditional format so if it returned false the cell would be red and if true the cell would be green.

I will try and do a rough set out below not sure if it will look the same once posted


A B E F
Number Text Header (hello) Header (Goodbye)
1 hello
1 hello
1 goodbye
2 goodbye
2 hello


So basically in column A there could be any number entered up 52.
In columns E1 and F1 I just need it to return true or false if there is a 1 in Column A with the text in Column B that matches the header of the cell in E or F.

This will be carried on in E2 and F2 looking to see if there is a 2 in Column A and the text next to it in Column B I wil do this all the way down to 52 in Column E and F.

Column E and F doesn't need to know how many instances of say 1 and said text there are it just need to know that there is at least one occurence or return false if not.

Hope this helps as I know it's not easy if you don't actually have the document.

Cheers

In reply to a post by camieabz:

It looks for a number in the range matching the number specified in D1.

I don't see how you can imply the criteria "<D1" OR ">D1" match, i.e. equals, the number D1.

They are looking (if they worked *) for the numbers, in the range, that are above or below the value of D1, but not the same as it.

If you really want to find out if there are none or more than 1 value of D1 in the range, all you have to say is:
= COUNTIF(A:A,D1) <> 1

* P.S. On my old Excel these criteria in text "s are just taken as text. It may be in newer versions that the are evaluated as expressions. Nevertheless, my other objection still holds true.

IMO you're splitting hairs on the method, rather than whether the solution is good or not. The formula works.

Another way, which keeps the formulae a little easier, is to insert another column say C and in that column concatinate the value if columns A and B. You can then use a simple countif to see if any of the values in column C are 4hello.

An example here

Sorry, but it makes no logical sense!

It may be relying on some quirk, like 5 iis TRUE and 0 is FALSE.

Do me a favour, in the =IF .. statement replace all the D1s by 4 (or whatever you were searching for) and see if it still works. I hope you agree they are equivalent.

Edited by XRaySpeX (Wed 01-Feb-12 13:34:55)

Hello again.

Im now back at the document.

=AND(COUNTIF($A:$A,1),COUNTIF($B:$B,"TEXT"))
This formula is returning a TRUE for all numbers even if in the formula above it is just asking to return a TRUE for the number 1 with TEXT next to it.


=VLOOKUP(1,$A:$B,2,FALSE)="TEXT"
This formula seems to work until I change the 1 to another number between 1 and 52 then it seems to return TRUE for some cells but not other.
For example =VLOOKUP(4,$A:$B,2,FALSE)="OTHERTEXT" returns a false even though in the data table ther is a 4 in column A with a OTHERTEXT in column B next to it.

It seems both these formulas nearly work so any more help is appreciated.


Thanks

Ok solved at last.

I ended up doing what phillippercival suggested and created another column in my data table and used =CONCATENATE(A2,B2) which gave me the combined data of the 2 columns.

Then in my matrix I used =AND(COUNTIF($C:$C,"1HELLO")) and for cells in the matirx that I wanted to look for a different number I would just change the 1 to a 4 or so on.

All seems to work so thanks to you 3 I can now save some time when I enter data even though I have probably spebt a couple of hours trying to the above.


Thanks

In reply to a post by Anonymous:

Then in my matrix I used =AND(COUNTIF($C:$C,"1HELLO")) and for cells in the matirx that I wanted to look for a different number I would just change the 1 to a 4 or so on.

Hint if ever you have to change anything on a spreadhseet then it should be typed into a box. So if the number you are looking for changes, then put it in a cell at the top somewhere, say I1, then reference I1 in your countif. Actually do the word as well.

That way you do not end up editing the formulae every time you change the number or word.

Example of the result here

edit: apologies if grandmothers and eggs spring to mind

Edited by philippercival (Wed 01-Feb-12 22:21:01)