Excel Excel: COUNTIF/Data questions

[godonnygo]

Hi guys, I'm not neccessarily new to Excel but I'm looking to utilise some more advanced features in order to increase efficiency, so I'll be firing off a few questions here, and appreciate any help

I have lists of values that contain data like "8.7 (55)" within the one cell. I know how to go Data > Text to Columns to separate them, but I can't separate all three values at once this way ("8", "7" and "55"). Not only this, but it overwrites whatever is in the right most cells...any idea how to fix this?

I'm also baffled at how to add more than one criteria to a COUNTIF function - not only within the same cell (like a number between 0 and 25), but within two different cells. For example, how do I get the side by side cells containing "1" and "2" to show up as part of the count?

Cheers in advance for any help you guys can muster

 

[TriplexDread]

Hi Donny, welcome to the site. There is a few Excel guru's here so be patient and we'll try sort you out

 

[godonnygo]

Thanks for the intro buddy - I look forward to it

 

[klaus]

I have lists of values that contain data like "8.7 (55)" within the one cell. I know how to go Data > Text to Columns to separate them, but I can't separate all three values at once this way ("8", "7" and "55"). Not only this, but it overwrites whatever is in the right most cells...any idea how to fix this?

You can do this via data>text to columns function as you can have multiple delimiters. Am attaching a jpeg snapshot of the convert text to column window.

Regarding overwrite, if you click next on the delimiter window, then there is a space where you can specify the cell where you want the text separation to happen (2nd jpeg file). Let me know if it does the trick.

 

[klaus]

I'm also baffled at how to add more than one criteria to a COUNTIF function - not only within the same cell (like a number between 0 and 25), but within two different cells. For example, how do I get the side by side cells containing "1" and "2" to show up as part of the count?

Try this link http://j-walk.com/ss/excel/tips/tip74.htm

 

[godonnygo]

You can do this via data>text to columns function as you can have multiple delimiters. Am attaching a jpeg snapshot of the convert text to column window.

Regarding overwrite, if you click next on the delimiter window, then there is a space where you can specify the cell where you want the text separation to happen (2nd jpeg file). Let me know if it does the trick.

This is the original data:


When I go to Data > Text to Columns here is what happens:


I could always move the changed data into far right columns but I'd prefer it to just expand and shift the existing columns to the right...

Thanks for your help so far

 

[klaus]

The function does not insert columns but you can record a macro and automate the steps. I recorded a macro that should do what you need but you gotta make sure that when you run the macro your cursor should be in the column B (containing the data).

Standard Disclaimer :
I DONT KNOW WHETHER THIS FORUM ALLOWS POSTING OF MACROS AND EVEN IF THE FORUM DOESNT HAVE A POLICY ON MACROS, IT'S NEVER A GOOD IDEA TO RUN MACROS WITHOUT FIRST GOING THROUGH IT TO ENSURE YOU UNDERSTAND WHAT IT DOES.


Its easy to record macros but like anything you need to play around till you get the desired effect.
Go to tools - macros - record new macro
make sure "relative cell reference" is on then the macro will record every action you make till you click on "stop recording macro" button.

Once you're satisfied that your macro does what you need then its just a matter of clicking on the macro. Its ideal for automating repetitive stuff AND you dont need to know anything about programming...at least, I dont!

Sub Macro1()
'
' Macro1 Macro
'
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell.Offset(1, 0).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=True, OtherChar:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1))
End Sub

 

[godonnygo]

Try this link http://j-walk.com/ss/excel/tips/tip74.htm

Good link but I always seem to get "1" even when there are 5 values that match both criteria exactly, even if I type in the same format as that example...

 

[klaus]

Good link but I always seem to get "1" even when there are 5 values that match both criteria exactly, even if I type in the same format as that example...

In this case excel needs to be told that it should regard the data as an array, therefore, instead of clicking just the <ENTER> button after writing the formula you need to press <CTRL><SHIFT><ENTER>

 

[godonnygo]

Nice...

Another question - in this formula:

=SUM((A2:A10="Jan")*(B2:B10="North"))

how do I make the "Jan" bit refer to a cell? so that it shows up as (A2:A10=B55) ... basically, so that whatever is in cell B55 forms part of the criterion.

 

[klaus]

how do I make the "Jan" bit refer to a cell? so that it shows up as (A2:A10=B55) ... basically, so that whatever is in cell B55 forms part of the criterion.

You're right, the formula would then be
=SUM((A2:A10=B55)*(B2:B10="North"))

When something is in double quotes (""), Excel uses it literally. For example ="B55" would be interpreted as B55; whereas =B55 would be interpreted as the contents of the cell B55.