Free Microsoft Excel 2013 Quick Reference

# Counting a column of 1s and 0s

Hey every one im new here and looking for some help on my reasearch project on droughts
so im using excel 2009 and heres my problem:

I have a column of 1s and 0s, 1 being when droughts occur and 0 being non drought occrence.

If a drought does occur i would like to have excell count the duration of the drought and put the length of the drought beside the first one
if any one could help it would be greatly appreciated and you could possibly be helping to save the world from drought

and I have a follow up question if this one gets answered

## Related Results

### Formula help for counting,with a column of dates and a column of n

Got a column of names and a corresponding column of dates. Am trying to
figure out how to write a formula that will a) select a pre-specified name
from the first column and count the number of times it occurs BASESD ON b)
the dates in the second column if they fall within a specified range.

If the date in the date column is within (the last week), then count the
name in the corresponding name column if it's (name goes here).

Both lists have data added to them daily and I don't want to get in and have
to move ranges or anything else manually every day.

How do I do that?

### Counting a column of times

I'm counting a column of times, but when it goes over 24 hours, I have a problem. Can I get Excel to sum times as eg:- 37.5 hours (as in decimal places of hours rather than minutes and seconds) AND in hh:mm:ss (result would be 37:30:00)

### Formulas : I need to count a column of VLookup/FALS

I have a column of VLOOKUP values, most fo which are "#N/A" - I need to count how many are NOT N/A. How do I do this?
Thanks!
Bryan

### Search for a word in a column of text_strings and...

Dear all

I'm stuck with a VLOOKUP-problem. I would like to search for a word (e.g. banana, E2) in a column of text strings (B2:B5, with B2=apple_banana_olive) and display the value of the cell (C2, MIX1) next to the text string with the match. In case it might not be clear, I put a sample in attachment...

I tried vlookup function (also with the "*" option) but that didn't work...;-(

Wannes

### A (tiny bit) complicated countif

Hi guys,

I have a column of 1s and 0s. I need to count (at bottom of the column) whenever I see a 1 followed by a 0 right below it. I have a hunch an array countif formula might work, but I don't know how to set it up, can you guys help?

Thanks!

Jube

### Read a column of names and place a number in the next cell

I want to automatically read a column of suburb names and add a column of
matching street directory map numbers. I have to do this monthly on several
different worksheets.

### Add 1 count to a column of numbers

If I have a column of numbers and I want to increase each number in that
column by 1, how do I do that? THANX!!

### Query:How do I take a column of dates and add six months?

insert a new column next to it say column B assuming your column of dates
are in column A
in cell B1 type
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
and copy and paste all the way down in column B

it adds 6 months to the current date and if that happens not to be a date
Hence Aug-31-99 + 6 months =
Mar-2-2000

"tigerswilson" <tigerswilson@discussions.microsoft.com> wrote in message
news:BD01450E-6FB5-4DA4-A73A-F927B099F8CA@microsoft.com...
> In Excel 2000 I have a column of dates. I need to know what all the dates
> are when 6 months is added to them. How can I achieve this?
>
> Thanks
>
> Mike

### Search for a word in a column of text_strings and...

Dear all

I'm stuck with a VLOOKUP-problem. I would like to search for a word (e.g. banana, E2) in a column of text strings (B2:B5, with B2=apple_banana_olive) and display the value of the cell (C2, MIX1) next to the text string with the match. In case it might not be clear, I put a sample in attachment...

I tried vlookup function (also with the "*" option) but that didn't work...;-(

Wannes

### I have a column of data and I'd like to transpose it to go across.

I have a column of data going down that I'd like to transpose to go across
the page so that I can add this info to data going across the page. I mean
for example, I have data Jan, Feb, Mar, going down the page and I'd like them
to be going across like 1st Month, 2nd Month, 3rd Month etc., but they are
going from top to bottom and I want them to go from right to left. I tried
the Transpose function, however, when I went to click on the arrow to the
Right of PASTE, there was no arrow.

Any suggestions??

John

### Find a certain month in a column of dates

I have a worksheet that is needing some VBA action. Here's the scenario.

The workbook contains about 45 sheets. Each sheet contains titles in row A. In column A there are a bunch of dates from certain points throughout the year. I need to be able to recognize a month and have it collect all the data from that month.

my real question is, i need a macro that can look through a column of dates and be able to recognize the month. so if i want may or december i would just type in 5 or 12 into an input box and that would be able to locate the month on the sheet

I'm not sure how to do a search for months in a column.

Also i cannot use a pivot table.

### Adding \$ signs to a column of numbers

I have a column of numbers that I'd like to convert to whole dollar amounts by prefixing the number with a '\$'.

When I highlight the column, and click on 'format', 'cells', 'custom', and type '\$#,###' nothing happens....

If I start a new spreadsheet and type in a column of numbers and do the same thing, it works....

Any ideas ?

### Convert a column of numbers from positive to negative in Excel

Excel 2003. We would like to copy a column of numbers and then convert the
whole column (each number in the column) from positive to negative. Can this
be done?

### How to sort a column of Randomly selected numbers?

I have a column of numbers and want to choose some of them by random. First,
I generate a coulmn of random numbers by RAND function and in next column
select from them by following command:
INDEX(\$A\$1:INDIRECT(C\$72),RANK(\$B1,\$B\$1:INDIRECT(C \$73)))
Now I want to sort these numbers (last column), let say Ascending. How can I
do this?
I am looking for a function or series of functions to perform sorting in a
different column without using Excel-built-in Sort commnad. The reason is
that, sort command doesn't work with randomly generated numbers, because each
time I try to use SORT command, Excel tries to regenerate them.
One poor solution is to copy/special paste (only values) the random numbers
and then use SORT command, but in this way I miss the regeneration function
of random numbers.
Any help is welcome
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

### Insert the same word at the start of a column of cells

I have a column of descriptions and want to insert the word Muratec in front
of each description. Does anyone know how to do this?
--
Thanks,
Ann

### Remove link fr a column of entries

I know how to remove a link from a single cell. Right click, hyperlink,
I have a column of cells and it is very cumbersome to remove cell by cell.
Is there any way to remove all cells in one action.?
TIA, Rasoul Khoshravan

### How to turn a column of numbers into interval range?

I have a column of ages and need to create a column of interval age range.
I need to create interval of 5 for ages 0 up to 105 (about 22)

ie (How can I generate the second column?):
*note: I have many many rows of records.

Age
0
0
0
1
2
3
4
5
6
6
6

Age interval
0-4
0-4
0-4
0-4
0-4
0-4
0-4
5-9
5-9
5-9
5-9

### Excel:a column of numbers,I want the highest shown automatically?

Can a conditional formula study a column of numbers and enter the highest or
lowest in the result cell? If yes how do you do it?

### Convert a column of numbers from positive to negative in Excel

Excel 2003. We would like to copy a column of numbers and then convert the
whole column (each number in the column) from positive to negative. Can this
be done?

### How do I make a column of checkboxes quickly?

I have a list of people my company has already mailed letters to (about 500)
.. I have another separate list of people that may or may not have been
mailed to from the first list. Is there a way to quickly creat a column of
checkboxes and then somehow crossreference these people that have already
been mailed to (both list have account numbers) so that we can exclude them
from our next mail out?

### Save a column of text as a file

How do I select a column of data and save it as an external file ( say, col.txt)?

One more thing, is there a way to have blank entries automatically skipped?!

### How do I take a column of dates and add six months?

In Excel 2000 I have a column of dates. I need to know what all the dates
are when 6 months is added to them. How can I achieve this?

Thanks

Mike

### Count a group of numbers but do not count duplicates

I need to count a column of numbers but do not want to count the duplicate
numbers. How do I do this?

### Creating a comma separated list from a column of numbers, omitting duplicates, spaces

What I'm trying to do is take information that is located in a column of numbers, and be automatically forwarded to a single cell with commas between each number, and omitting any duplicates and spaces (empty cells), with them also being in numerical order.

For example:
8765
8765
9803
2011
8765
0008

in six columns would be taken to a single cell as:
0008, 2011, 8765, 9803

In the sample workbook attached, I have information in sheet two, column A and needs to be sent automatically as a comma'ed list to sheet one, B1

On sheet two, the space for potential data input is between A2 and A20

I can include my actual project (with dummy information) if the sample project doesn't explain clearly enough what I need to accomplish.

I only have limited knowledge of programing with VBA,so a code snippet would be preferred.