Using Countif ? to count number of rows.

  • Thread starter Thread starter ronenpl
  • Start date Start date
R

ronenpl

Hi,



vcr
5
5
5
6
2

tv
4
41

DVD
6
6
2


the digits appear on row E. I need excel to count the number of row
for VCR when I specify VCR, i.e. 5. I than need it to count the nubme
of rows for TV when I specify TV, i.e. 2. And so on...

I don't think just count will do it. Maybe there is a variation o
countif that can do this?

Thanks for all your help guys
 
You could have stayed in your original thread...

In G2 enter:

=MATCH(9.99999999999999E+307,E:E)

In G3 enter: vcr [ which is the item of interest. ]

In G4 enter:

=MATCH(G3,E:E,0)

In G5 control+shift+enter (that is, confirm the formula entry with
control+shift+enter instead of just with enter)...

=MIN(IF(ISTEXT(INDEX(E:E,G4+1):INDEX(E:E,G2)),ROW(INDEX(E:E,G4+1):INDEX(E:E,
G2))))

In G6 enter:

=COUNT(INDEX(E:E,G4):INDEX(E:E,IF(G5,G5,G2)))
 
Hi
is your data just in ONE column?. If yes I'd suggest you change your
spreadsheet layout slightly. e.g. one column for the type (vcr, tv,
etc) and one column for your data. In this case COUNTIF could easily be
used
 
Have you tried

=COUNTIF(E:E,5)

etc.?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Frank,

My data is the way you suggested it. The type is in column
(VCR,TV,etc) and the data is in column E. However How do I use Counti
to count only the rows that apply to vcr, tv, etc..

There are blank rows between each section. i.e. after the data fo
VCR there are a few blank rows
 
ronenpl > said:
Hi Frank,

My data is the way you suggested it. The type is in column C
(VCR,TV,etc) and the data is in column E. However How do I use Countif
to count only the rows that apply to vcr, tv, etc..
Click to expand...

Quite disturbing way of communicating. Why didn't you mention that the items
were in C while the associated figures are in E?

Is the layout like this...

VCR 3
VCR 7
VCR 8

TV 5
TV 9

DVD 8
DVD 7

or like this

VCR 3
7
8

TV 5
9

etc.

And, just to make sure: You want to count how many figures are associated
with VCR, etc., right?
There are blank rows between each section. i.e. after the data for
VCR there are a few blank rows
Click to expand...
[...]
 
Hi Frank,

but the data is in column E. I need it to refer to VCR in Colunn C an
give me the number of raw of date from column E that pertain to VCR.
Hence for VCR the result would be 5, for TV the result would be 2. fo
DVD it would be 3.
I have attached a copy of the spreadsheet.

Thanks for your help again.


Frank said:
*Hi
use
=COUNTIF(C1:C100,"vcr")
Click to expand...

Attachment filename: #ofrows.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48437
 
Hi Aladin,

I have attached a file to help explain what I need.

Thanks for your help.



Aladin said:
message
Hi Frank,

My data is the way you suggested it. The type is in column C
(VCR,TV,etc) and the data is in column E. However How do I us Countif
to count only the rows that apply to vcr, tv, etc..
Click to expand...

Quite disturbing way of communicating. Why didn't you mention tha
the items
were in C while the associated figures are in E?

Is the layout like this...

VCR 3
VCR 7
VCR 8

TV 5
TV 9

DVD 8
DVD 7

or like this

VCR 3
7
8

TV 5
9

etc.

And, just to make sure: You want to count how many figures ar
associated
with VCR, etc., right?
There are blank rows between each section. i.e. after the dat for
VCR there are a few blank rows
Click to expand...
[...]
Click to expand...

Attachment filename: #ofrows.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48438
 
Are you saying that data is structured as

C E
VCR 5
VCR 5
VCR 5
VCR 6
VCR 2
TV 4
TV 41
etc.

or
VCR 5
5
5
6
2
TV 4
41
etc.

If the former, Frank's formula works.

By the way, don't attach files, we would be mad to open them in today's
virus climate.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

ronenpl > said:
Hi Frank,

but the data is in column E. I need it to refer to VCR in Colunn C and
give me the number of raw of date from column E that pertain to VCR.
Hence for VCR the result would be 5, for TV the result would be 2. for
DVD it would be 3.
I have attached a copy of the spreadsheet.

Thanks for your help again.
Click to expand...
 
Hi
I won't open attachments :-)
Please post some example rows in plain text to this NG (or just have a
look at Bob's or Aladin's question how your data is strcutured)

--
Regards
Frank Kabel
Frankfurt, Germany

ronenpl > said:
Hi Frank,

but the data is in column E. I need it to refer to VCR in Colunn C and
give me the number of raw of date from column E that pertain to VCR.
Hence for VCR the result would be 5, for TV the result would be 2. for
DVD it would be 3.
I have attached a copy of the spreadsheet.

Thanks for your help again.
Click to expand...
 
Hi Bob,

its the latter. In Colunn C VCR appear only once, and the data appear
on Column E one number per row.

Such as:

C E
VCR 2
3
44
5

TV 2
5
6

etc.


Bob said:
*Are you saying that data is structured as

C E
VCR 5
VCR 5
VCR 5
VCR 6
VCR 2
TV 4
TV 41
etc.

or
VCR 5
5
5
6
2
TV 4
41
etc.

If the former, Frank's formula works.

By the way, don't attach files, we would be mad to open them i
today's
virus climate.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Hi Frank,

The type in column C appears only once.
as followes:

C E
VCR 4
6
23

TV 5
4

etc.


thanks



Frank said:
*Hi
I won't open attachments :-)
Please post some example rows in plain text to this NG (or just hav
a
look at Bob's or Aladin's question how your data is strcutured)
Click to expand...
 
The simplest way is to copy the values in C down to fill in the blanks. You
could use conditional formatting to hide it if you only want to see the
first instance.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

ronenpl > said:
Hi Frank,

The type in column C appears only once.
as followes:

C E
VCR 4
6
23

TV 5
4

etc.


thanks
Click to expand...
 
In G2 enter:

=MATCH(9.99999999999999E+307,E:E)

In G3 enter: vcr [ the item of interest ]

In G4 enter:

=MATCH(G3,C:C,0)

In G5 control+shift+enter:

=MIN(IF(ISTEXT(INDEX(C:C,G4+1):INDEX(C:C,G2)),ROW(INDEX(C:C,G4+1):INDEX(C:C,
G2))))

In G4 enter:

=COUNT(INDEX(E:E,G4):INDEX(E:E,IF(G5,G5,G2)))

It's the solution I already gave you, which is adapted to look at also
column C.

ronenpl > said:
Hi Aladin,

I have attached a file to help explain what I need.

Thanks for your help.



Aladin said:
message
Hi Frank,

My data is the way you suggested it. The type is in column C
(VCR,TV,etc) and the data is in column E. However How do I use Countif
to count only the rows that apply to vcr, tv, etc..
Click to expand...

Quite disturbing way of communicating. Why didn't you mention that
the items
were in C while the associated figures are in E?

Is the layout like this...

VCR 3
VCR 7
VCR 8

TV 5
TV 9

DVD 8
DVD 7

or like this

VCR 3
7
8

TV 5
9

etc.

And, just to make sure: You want to count how many figures are
associated
with VCR, etc., right?
There are blank rows between each section. i.e. after the data for
VCR there are a few blank rows
Click to expand...
[...] *
Click to expand...

Attachment filename: #ofrows.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=484384
Click to expand...
 
Back
Top