counter

[JulieD]

Hi

if i'm understanding you correctly, you can use the COUNTIF function
=COUNTIF(A1:A100,1)
this will count the number of times the number 1 appears in the range
A1:A100

hope this helps
Cheers
JulieD

 

[wuddzz]

How can I set up a counter in excel. Eg, I want to count the tota
number of times when a cell value is 1. This seems like it should b
easy but I can't figure it out.

wuddz

 

[Guest]

Hi, excuse me for butting in but I would like to extend this thread a bit:

How would we (using the aboveidea) test for multiple occurences of a factor
(1 in your example) ?

E.g of data:

A B
100 1
101 1
102 1
110 1
210 2
220 2
300 3


Answer required is there are 3 code rand 1XX, 2 code ranges 2XX and 1 code
range 3XX.

See what I am getting at?

Speedy

Hi

if i'm understanding you correctly, you can use the COUNTIF function
=COUNTIF(A1:A100,1)
this will count the number of times the number 1 appears in the range
A1:A100

hope this helps
Cheers
JulieD

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(INT(A1:A100/100)=1))
and
=SUMPRODUCT(--(INT(A1:A100/100)=2))
....

 

[Guest]

Hello Frank,

Yes, I see but the problem is a little more complicated than in the example
data I have given in reality. The "numbers" are really Strings i.e. "1"
would in fact be "bank", "2" = Investments" etc. So I would prefer a
count(er) type technique which would tell me how many rows with the B column
set as "bank", "investments" etc. they are in the data.

Also this is to be included in a VBA routine and I need to keep a variable
for CountBankRows, CountInvestmentsRows etc. which I will use in later logic
to determine how many rows I need to write (copy) the data to another sheet.

Have I explained things clearly enough?

Stéphane

 

[Frank Kabel]

Hi
why VBA?. For a non VBA solution use
SUMIF
or
COUNTIF

You may also take a look at pivot tables for this

 

[Guest]

Frank,

I need to do it in VBA because the other parts of the model are already
coded in VBA. The main application populates various detail sheets from an
input sheet. A fair VBA developpment has already taken place with the good
help of a number of people from the forum.

Stéphane

 

[Frank Kabel]

Hi
then you couls use in VBA:
application.worksheetfunction.sumif(...)

 

[Myrna Larson]

Just a comment:

You make a lot of extra work for people when you simplify your data, like
using numbers when it's actually text. The solution for one isn't the same as
for theother.

 

[Guest]

Hi Myrna, fair enough but I find that since there is a bit of a start and go
away approach to answering question it is not always easy to get what ones
want in one thread. I end up reposting the same ever longer model and
questions. This has the benfit of getting more people's input but also has
limitation as a problem solving apporach.

Don't get me wrong I greatly appreciate the assistance given by all the
experts on the Forum. But I sometimes get the feeling that the experts get
quickly frustated by us novices who while not knowing the ins and out of
Excel do have some idea of what they are trying to achieve.

It is unfortunately a bit of a trial and error process where one picks up a
new usefull idea or technique and would like to explore it to see how it can
be integrated into ones main project. I appologise if this process is a bit
messy.

I never know if its best to publish the whole problem or break it down into
manageable units and try and get a concptual solution leaving the detail
cdong to me or whether the helpers want the whole problem and detail spec to
work on?

Give us guidance on this ?

Again my thanks to Otto, Frank and off course yourself for the help in the
various threads.

Stéphane

 

[Guest]

Hello Frank,

Sorry If I mislead you a little on the data to be handled.

With strings I found that the original idea of =COUNTIF(G1:G5,"Investments")
works OK.

But how would one go about handling multiple cases of strings i.e.
"investments", "Bank" etc. and keep the result of the count in a variable for
each type to be used by the other part of the VBA model?

Stéphane

 

[Frank Kabel]

Hi
in VBA you could use
result=application.evaluate("=SUMPRODUCT(....)")

and create a SUMPRODUCT formula which gets this data

 

[Guest]

Thanks.

Stéphane

Hi
in VBA you could use
result=application.evaluate("=SUMPRODUCT(....)")

and create a SUMPRODUCT formula which gets this data