#### How do I count the number of cells

```I have  several dozens columns and 52 rows containing Data.

All the columns are labled Odd or Even
In the final (right most) Column I want to count the number of times that
that coulmn has data in it
The last row belows shows what the answer should be

Total	Total
Odd	Even	Odd	Even	Odd	Even	Odd
5	1	2		?????	?????
1	7		6	3	?????	?????
5	1	2		?????	?????
1	2	9		3	1	3

```
 0
Utf
12/1/2009 8:28:01 PM
excel.newusers 15348 articles. 2 followers.

7 Replies
1023 Views

Similar Articles

[PageSpeed] 57

```=COUNTA()

- will count the number of non blank cells.

In the brackets place the range name (for example A1:A2).

Thanks.

"Alan" wrote:

> I have  several dozens columns and 52 rows containing Data.
>
> All the columns are labled Odd or Even
> In the final (right most) Column I want to count the number of times that
> that coulmn has data in it
> The last row belows shows what the answer should be
>
> 												Total	Total
> Odd	Even	Odd	Even	Odd	Even	Odd
> 	5	1	2		?????	?????
> 1	7		6	3	?????	?????
> 	5	1	2		?????	?????
> 1	2	9		3	1	3
>
>
```
 0
Utf
12/1/2009 8:50:01 PM
```but that will also count the even columns

I need to count the number of columns that only have "even" on the top and
data in them

and count the rows seperately that say "odd"

"trip_to_tokyo" wrote:

> =COUNTA()
>
>  - will count the number of non blank cells.
>
> In the brackets place the range name (for example A1:A2).
>
>
> Thanks.
>
> "Alan" wrote:
>
> > I have  several dozens columns and 52 rows containing Data.
> >
> > All the columns are labled Odd or Even
> > In the final (right most) Column I want to count the number of times that
> > that coulmn has data in it
> > The last row belows shows what the answer should be
> >
> > 												Total	Total
> > Odd	Even	Odd	Even	Odd	Even	Odd
> > 	5	1	2		?????	?????
> > 1	7		6	3	?????	?????
> > 	5	1	2		?????	?????
> > 1	2	9		3	1	3
> >
> >
```
 0
Utf
12/1/2009 9:05:02 PM
```Perhaps this to count across row 1:

=COUNTIF(1:1,"even")

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Alan" wrote:

> but that will also count the even columns
>
> I need to count the number of columns that only have "even" on the top and
> data in them
>
> and count the rows seperately that say "odd"
>
>
>
> "trip_to_tokyo" wrote:
>
> > =COUNTA()
> >
> >  - will count the number of non blank cells.
> >
> > In the brackets place the range name (for example A1:A2).
> >
> >
> > Thanks.
> >
> > "Alan" wrote:
> >
> > > I have  several dozens columns and 52 rows containing Data.
> > >
> > > All the columns are labled Odd or Even
> > > In the final (right most) Column I want to count the number of times that
> > > that coulmn has data in it
> > > The last row belows shows what the answer should be
> > >
> > > 												Total	Total
> > > Odd	Even	Odd	Even	Odd	Even	Odd
> > > 	5	1	2		?????	?????
> > > 1	7		6	3	?????	?????
> > > 	5	1	2		?????	?????
> > > 1	2	9		3	1	3
> > >
> > >
```
 0
Utf
12/1/2009 9:15:03 PM
```Maybe this?

Odds:     =SUMPRODUCT((A\$1:H\$1="Odd")*(A2:H2<>""))

Evens:    =SUMPRODUCT((A\$1:H\$1="Even")*(A2:H2<>""))

Change the A thru H example columns to the actual column limits you are
using.

--
Rick (MVP - Excel)

"Alan" <Alan@discussions.microsoft.com> wrote in message
news:F205114C-1986-4849-81C8-5D85B0F441FF@microsoft.com...
> but that will also count the even columns
>
> I need to count the number of columns that only have "even" on the top and
> data in them
>
> and count the rows seperately that say "odd"
>
>
>
> "trip_to_tokyo" wrote:
>
>> =COUNTA()
>>
>>  - will count the number of non blank cells.
>>
>> In the brackets place the range name (for example A1:A2).
>>
>>
>> Thanks.
>>
>> "Alan" wrote:
>>
>> > I have  several dozens columns and 52 rows containing Data.
>> >
>> > All the columns are labled Odd or Even
>> > In the final (right most) Column I want to count the number of times
>> > that
>> > that coulmn has data in it
>> > The last row belows shows what the answer should be
>> >
>> > Total Total
>> > Odd Even Odd Even Odd Even Odd
>> > 5 1 2 ????? ?????
>> > 1 7 6 3 ????? ?????
>> > 5 1 2 ????? ?????
>> > 1 2 9 3 1 3
>> >
>> >

```
 0
Rick
12/1/2009 9:28:38 PM
```That just counts the number of times the word even appears,  let me try to
rephrase my problem

I have several columns (over 100) that say either "odd" "even", or no
nothing at the top (row1)
I need a formula that will read across row 2 starting at column 1 look up to
see if "even" is written there, if it is and there is a value in that cell
(c1,r2) then I need to count that cell, then I need to look in Column 2, if
"even" is written there I need to count that cell as well (c2,r2).  repeat to
Column (DA)

C1         C2          C3      C4          Count    Count
evan     Odd
R1        Even      Odd       Odd     neg

R2          1           1            1        4             formula?

R3                       2            2        3             formula?

R4           9          8            8         0            1            2
this is the result I need

"JBeaucaire" wrote:

> Perhaps this to count across row 1:
>
> =COUNTIF(1:1,"even")
>
> --
> "Actually, I *am* a rocket scientist." -- JB
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Alan" wrote:
>
> > but that will also count the even columns
> >
> > I need to count the number of columns that only have "even" on the top and
> > data in them
> >
> > and count the rows seperately that say "odd"
> >
> >
> >
> > "trip_to_tokyo" wrote:
> >
> > > =COUNTA()
> > >
> > >  - will count the number of non blank cells.
> > >
> > > In the brackets place the range name (for example A1:A2).
> > >
> > >
> > > Thanks.
> > >
> > > "Alan" wrote:
> > >
> > > > I have  several dozens columns and 52 rows containing Data.
> > > >
> > > > All the columns are labled Odd or Even
> > > > In the final (right most) Column I want to count the number of times that
> > > > that coulmn has data in it
> > > > The last row belows shows what the answer should be
> > > >
> > > > 												Total	Total
> > > > Odd	Even	Odd	Even	Odd	Even	Odd
> > > > 	5	1	2		?????	?????
> > > > 1	7		6	3	?????	?????
> > > > 	5	1	2		?????	?????
> > > > 1	2	9		3	1	3
> > > >
> > > >
```
 0
Utf
12/1/2009 9:30:01 PM
```He Shoots he scores

Thanks Rick

Excellent help

"Rick Rothstein" wrote:

> Maybe this?
>
> Odds:     =SUMPRODUCT((A\$1:H\$1="Odd")*(A2:H2<>""))
>
> Evens:    =SUMPRODUCT((A\$1:H\$1="Even")*(A2:H2<>""))
>
> Change the A thru H example columns to the actual column limits you are
> using.
>
> --
> Rick (MVP - Excel)
>
>
> "Alan" <Alan@discussions.microsoft.com> wrote in message
> news:F205114C-1986-4849-81C8-5D85B0F441FF@microsoft.com...
> > but that will also count the even columns
> >
> > I need to count the number of columns that only have "even" on the top and
> > data in them
> >
> > and count the rows seperately that say "odd"
> >
> >
> >
> > "trip_to_tokyo" wrote:
> >
> >> =COUNTA()
> >>
> >>  - will count the number of non blank cells.
> >>
> >> In the brackets place the range name (for example A1:A2).
> >>
> >>
> >> Thanks.
> >>
> >> "Alan" wrote:
> >>
> >> > I have  several dozens columns and 52 rows containing Data.
> >> >
> >> > All the columns are labled Odd or Even
> >> > In the final (right most) Column I want to count the number of times
> >> > that
> >> > that coulmn has data in it
> >> > The last row belows shows what the answer should be
> >> >
> >> > Total Total
> >> > Odd Even Odd Even Odd Even Odd
> >> > 5 1 2 ????? ?????
> >> > 1 7 6 3 ????? ?????
> >> > 5 1 2 ????? ?????
> >> > 1 2 9 3 1 3
> >> >
> >> >
>
> .
>
```
 0
Utf
12/1/2009 10:09:01 PM
```Good afternoon,

To just count how many columns contain data, use:

=COUNTA(RangeToCount)

For Example:

A1  5
B1
C1  10
D1
E1  15
F1  =COUNTA(A1:E1) will give you 3.

Kind regards,
James
---
jamotarpey[at]gmail[dot]com

Alan wrote:
>I have  several dozens columns and 52 rows containing Data.
>
>All the columns are labled Odd or Even
>In the final (right most) Column I want to count the number of times that
>that coulmn has data in it
>The last row belows shows what the answer should be
>
>												Total	Total
>Odd	Even	Odd	Even	Odd	Even	Odd
>	5	1	2		?????	?????
>1	7		6	3	?????	?????
>	5	1	2		?????	?????
>1	2	9		3	1	3
>

--
---
jamotarpey[at]gmail[dot]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200912/1

```
 0
Jamo
12/7/2009 2:55:42 PM

Similar Artilces:

Limited number of mail
One client want to limit the number of mails a user can send for day or month using Exchange. Any one knows about a product that do that? Thanks Leo http://www.babysbitter.com -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Leo" <anonymous@discussions.microsoft.com> wrote in message news:235CCDD4-C8AE-4C71-A36F-8ED188ED99E2@microsoft.com... > One client want to limit the number of mails a user can send for day or month using Exchange. Any one knows about a product that do that? > Thanks, > Leo ...

how do I enter numbers in excel using speech Recognition?
I am trying to use speech recognition to speed up number entering in excel file. every time I say " one" or " number one" it writes the words and not numbers. what should I do? Thanks for answering. ...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Can I copy a table from Word into one cell in Excel without losing data? (Office 97)
I would like to copy a small table from Word into one cell in an Excel worksheet. The first column of the table is a list of numbers. I tried converting the table into text with manual line breaks and tab stops to divide columns and rows, but that didn't solve my problem. Excel pastes the data into several rows. When I try to merge them, I get a warning that the selection contains multiple data values, and merging into one cell keeps the upper-left most data only. What I tried that didn't work: * Formatting the Excel cells as text before pasting the data. * The various options for ...

Any way to round using number format codes?
I'd like to have numbers displayed as being rounded up to the next 5000. So, if a cell contains 92,345...I'd like it to display 95,000. Is there any way to do this with just number format codes (instead of with the worksheet fxns?) Thanks in advance. sert Not with format codes. You can display in thousands but not to the nearest 5000. On Mon, 18 Oct 2004 16:15:47 -0400, sert <basurero3@yahoo.com> wrote: >I'd like to have numbers displayed as being rounded up to the >next 5000. > >So, if a cell contains 92,345...I'd like it to display 95,000. > >I...

PPT 07 from PPT03 imported slides doe not 'insert' slide number
Since migrating to MS Office 2007, my old slides when upgraded to PPT 07 will not allowme to add a page numberon the slides. I have saved the slide show to a new show, I have tried placin gin the field on Master View, I have simply tied the 'button' to insert slide number. I get no results, after multiple multiple efforts. Anyone fiind a work around? I do not need to re-build 60-slides for training just to have a page number. Hi, You need to ensure that the slide number placeholder is set in the master template. Slide master/ master layout/ tick slide number >...

key board short cut for copying formula from one cell to another
Lets there is a formual in cell lets say "c1"., If I want to copy the formula from c1 to c2, I place the cursor (using mouse) on the bottom right corner fo c1 and drag it to c2. Is there a key board short cut of doing the above. Pls help,., thanks Mario, select C1 and C2, then Ctrl+D. If C1 is the active cell, press Shift+Down Arrow to highlight cells below, then Ctrl+D to copy the formula. To copy to the right rather than down, Ctrl+R. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "mario" <anonymous@discuss...

Change the assigned chart number
If you use the Macro Recorder and select several charts (in the same worksheet) in secession you can see that Excel has assigned a name to each chart such as Chart 1, Chart 2, ect.. Is there a way to rename say Chart 15 to Chart 3, assuming Chart 3 does not already exist? -- Al Eaton aleaton@bak.rr.com To manually rename a chart object: Hold the Ctrl key, and click on the chart to select it Click in the Name box, to the left of the formula bar Type a new name, or edit the existing name Press the Enter key To programmatically rename a chart object: ActiveSheet.ChartObjects("Ch...

Taking numbers off a website and importing them into Excel? Possible?
I have no idea if this is possible, but please help if you have a idea. I need to get some data off of a website, particularly a certain set o numbers from this website, and enter them into data in Excel. Here is an example of the website I need: http://www.covers.com/data/ncf/matchups/g8_main_1.aspx There is a table near the top of the website which lists the tw teams.... I need some way to automatically go to this website, find Syracuse, an then cross reference that with two sets of numbers, AF and AA, whic works out to be 20.0 and 25.7 I then need these numbers to be transported int...

Count If Cell Value Starts with...
I need to count the number of cells in a range whose leftmost character is an S, or an L. I attempted to use the formula to count the cells starting with "S": {=SUMPRODUCT(LEFT(H7:H134,1)="S")} However it returned a zero, which is not correct. Does anyone know how I could do this without writing a VB custom function? Thanks! Scott Scott, The Boolean expression LEFT(H7:H134,1)="S" returns TRUE or FALSE. We need to coerce that to its equivalent 1 or 0 for SUMPRODUCT to add 'em up. An easy way is with double negation: {=SUMPRODUCT(--(LEFT(H7:H134,1)=...

select the number of rows using shortcut keys
Not able to select the number of rows using shortcut keys (SHIFT+DOWNARROW OR UPARROW) So what happens when you hit SHIFT + Downarrow? Should move down and select the next row as well as start row. SHIFT + END + Downarrow should select to next blank row. Gord Dibben Excel MVP On Wed, 10 Nov 2004 05:03:01 -0800, Naidu Samudrala <Naidu Samudrala@discussions.microsoft.com> wrote: >Not able to select the number of rows using shortcut keys (SHIFT+DOWNARROW OR >UPARROW) ...

count based on another field
I have a table in my database which records when a case is marked by an assessor. The table shows the case number, the assessor ID that the case has been assigned to and also an indicator to determine whether or not the case is closed. I need the query to show: The assessor ID number The number of cases assigned to a particular assessor the number of cases assigned to a particular assessor that are incomplete The first two are fine, however, how do I limit the last part of the query to show only the cases assigned to each assessor? thanks <barrynichols@gmail.com> wrote in message...

displaying a single text character for a number value on a subform
I have a form called "clients" that has a combo box field for an e-mail subscription. The choices are unsubscribed (underlying value=0), subscribed (underlying value=1) & not subscribed (underlying value=2). This info also shows on a subform on a form called "companies". I would like for the data onthis "companies" subform to show U for value 0, S for value 1 and N for value 2. I am stupped in how to do this. Thanks for any hints/help Ken Gehle "Ken Gehle" <KenGehle@discussions.microsoft.com> wrote in message news:6E83049A-1308-4E08-9C...

Total number of sent/received mails through exchange server in one day
Hi! How can I see a total number of sent/received mails through exchange server in one day? Where can I generate a report? Tnx! Use message Tracking logs or 3rd party products, that can analyze them. Melia, Promodag and http://wm.quest.com/products/messagestats/ might be used. HrVOjE wrote: > Hi! > > How can I see a total number of sent/received mails through exchange > server in one day? Where can I generate a report? Tnx! ...

Hi All If I have Data in cells A1 to A10 or C1 to C23 etc and I want to paste more Data along side, to B1 to B10 or D1 to D23. Even better would be if I could paste to either side of the data. Is there away to do this without having to highlight those cells, this would save me allot of time. Thanks in Advance Dave, When you paste, it pastes the amount of stuff you've copied. If you've copied 10 vertical cells, that's what it's gonna paste. You need only select the top-left-most cell before you do a paste. -- Earl Kiosterud mvpearl omitthisword at verizon period ne...

Sorting numbered system that relies on decimals
eg. This is the result I am looking for: TS-501.9.8 TS-501.9.9 TS-501.9.10 TS-501.9.11 TS-501.10.1 TS-501.10.2 TS-501.11.1 This is the result I am getting: TS-501.10.1 TS-501.10.2 TS-501.11.1 TS-501.9.10 TS-501.9.11 TS-501.9.8 TS-501.9.9 As you can see the numbers between the dots ascend from 1, however excel takes them as decimals (eg 0.1 = 0.10 = 0.100). Suggestions appreciated! --- Message posted from http://www.ExcelForum.com/ Hi It's possible to do using helping column, and a huge formula in it. But why not to convert your column into form TS-501.09.08 etc. Then you haven'...

How to combine text of two cells
I have two cells containing numbers like 10 & 13 I need to combine them in one cell so they display 10/13 Assuming that both number cells are formatted "general" here's how you do it using this format. =A1&"/"&B1 Assumes: Cell A1 = 10 Cell B1 = 13 The numbers are assumed to be text and can be combined by using the "&" key. Any additional text that you want to add can be done by enclosing in quatation marks. >-----Original Message----- >I have two cells containing numbers like >10 & 13 >I need to combine them in one ce...

Lookup and count in the same formula
Hello, I need a formula that looks up a value in row 1 on sheet2 and returns the count of a range in the colmun that matches the lookup in sheet1. For example: Sheet2 - Feed Sheet Smithfield RSM John Doe ASM Jenn Smith ASM2 Brian Smith So Smithfield has 1 RSM and 2 ASMs Sheet1 RSM ASM Smithfield 1 2 So I want a formula to return the count of how many RSM's and ASM's are in a specific location. I have this: =(HLOOKUP(A1,Sheet2!A1:A6,(COUNTA(Sheet2!A5:A6)),FALSE)) but it's just retur...

Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell? Hi Carter one option - well two really: if the information is in cells A1, B1 & C1 and you want it combined into D1 then in D1 type =A1 & " " & B1 & " " & C1 this will give you the contents of each with a space between OR =CONCATENATE(A1," ",B1," ",C1) which will give you exactly the same thing Hope this helps Cheers JulieD "Carter" <anonymous@discussions.microsoft.com> wrote in message news:5fc401c48ac0\$9cc86830\$a301280a@phx.gbl... > How do I combine tex...

automatic receiving of serial numbers
Almost all of our items have serial numbers. One of our stores is actually the central distribution warehouse, and all of our stores are connected via RMS Headquarters. When creating a Transfer Out form from the warehouse we have to select the serial numbers, and would like the serial number data to be sent along with the Transfer In form to the receiving store so that the serial number data does not have to be re-entered at the receiving store. Some technicians at the RMS distributor in Australia told us to unclick the Serial Numbers option "Require Serial Number when Receiving...

Bring back line numbers in Integration Manager script windows V10
I have noticed in V10 Integration Manager the ability to “Show Line Numbers” in the script windows has been removed. I have found this feature to be beneficial in the past and find it painfully missed in V10 IM. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http:...

Sorting page numbers
Is there a simple way of sorting pages back into numeric order after building a large spread sheet so they appear in ascending order on the monitor. Without renumbering that is Thanks Mike Hi Mike, Your question is not clear to me, so here are three interpretations even if it is a stretch compared to even what you indicated. If you sorted the rows into a different order and want to restore them you can restore the order if you previous numbered your rows by placing a 1 on a new column (helper column) and used the fill-handle to place 2 into next cell, and 3 into next, etc. If you mean...

How do i transfer cell data from one sheet to the other in Excel
I have Sheet1 and Sheet2. In Sheet1, Column A, I merged rows 1-4 as one cell to contain the ID # for the person Column B: name in Column B1, address in B2, city in Column B3, and phone # in Column B4. When I entered the ID# in Column A of merged rows 1-4 in Sheet2, I need the information from Column B1-B4 of Sheet1 to transfer to Sheet2, Column B1-B4 automatically. I would appreciated if someone can give me a hand....thanks. I hate merged cells, but... you need 4 separate formulas on Sheet2, B1:B4. In B1 =OFFSET(Sheet1!\$A\$1,MATCH(\$A\$1,Sheet1!\$A:\$A,0)-1,1) in B2 =OFFSET(Sheet1!\$A\$1,MA...

Trim String after last number
I have a string that is both text and numeric (ie:A123BC), the last charectors afte the number does not always equal 2 so a right of left formula will not cut it. Besides the Right and left function how can I drop a formula that will always trim after the last numeric value, so that A123BC will return A123. Thanks fo rthe help Bobby Could you provide some more description of the strings. Do they fall within some minimum and maximum length? Do the letter on the end fall within some minimum and maximum length Depending on the complextiy of the data set, this can probably be done with some c...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...