Help needed with formulea.

C

Chris Mitchell

Using Exel 2002 SP3.

I can use the Countif function to count the number of cells in a contiguous
range that contain my specified value.

How can I modify this so that instead of looking within a contiguous range
of cells, eg B5:B9 the formulea will look at group of specified,
non-contiguous cells, eg A1 and A3 and B5?

I've tried putting the group of cells in ()s within the formulea, and using
":" and "," as seperators, but no joy.

Am I using the wrong function to try to do this?

If so what should I be using and how?

TIA
 
A

Anne Troy

Instead of trying to work out a formula like that, I would be doing some
serious consideration on laying out your data differently. You will be much
happier if you put your data in individual rows and columns. If you need to
SHOW your data in some other manner, then maybe mail merging is what you
need to do.

I'm sorry I can't help with your formula, tho.
________________________

**** Hope it helps! ****

~Dreamboat
www.VBAExpress.com/forum
************************
 
F

Frank Kabel

Hi
is there a logic in you non contigenous range. e.g. every second cell
in a column?
 
A

Aladin Akyurek

If the data and the criterion value numeric, you can have:

=INDEX(FREQUENCY((A1,A3,B5),{0.999999999,1}*E1),2)

which is derived from:

http://tinyurl.com/67aeb

E1 houses the criterion value whose occurrences you want to count.

For any value...

=SUMPRODUCT(COUNTIF(INDIRECT({"A1","A3","B5"}),E1))

which picks up:

http://tinyurl.com/5x59t
 
C

Chris Mitchell

Every third cell in a row in a range.

Contents are restricted to three entry types "blank" "WT" & "/". I want to
count the number of "/"s in three interspersed sets eg, Set 1 A, Set 2 A,
Set 3 A, Set 1 B, Set 2 B, Set 3 C, Set 1 C, Set 2 B, Set 3 C and get totals
for Set 1, Set 2 and Set 3.

This will be repeated many times throughout the spreasheet which will
probably goto something like column CZ or beyond.
 
C

Chris Mitchell

Contents are restricted to three entry types "blank" "WT" & "/". I want to
count the number of "/"s in three interspersed sets eg, Set 1 A, Set 2 A,
Set 3 A, Set 1 B, Set 2 B, Set 3 C, Set 1 C, Set 2 B, Set 3 C and get totals
for Set 1, Set 2 and Set 3.

This will be repeated many times throughout the spreasheet which will
probably goto something like column CZ or beyond.
 
C

Chris Mitchell

Thanks Aladin.

Got it with

=SUMPRODUCT(COUNTIF(INDIRECT({"F3","I3","L3","O3","R3","U3"}),"/"))

Only outstanding problem is that when I put this in the first Total cell it
works fine, but when I drag it to fill adjacent Total cells the references
of the cells to be added doesn't increment by one, ie it doesn't become

=SUMPRODUCT(COUNTIF(INDIRECT({"G3","K3","M3","P3","S3","V3"}),"/"))

Where the Column references have increased by one over the original.

Can this be modified in some way such that it will do this, and also such
that I can copy and paste the formulea into different parts of the
spreadsheet and it will pick up the references relative to where I paste it?
If so how.

I'm not going to be able to get online for a week or so, so if you do
respond to this please email a copy of your answer to me at

[email protected], remove MAPSON (NO SPAM backwards) to
reply

so I don't miss it whilst I'm away.

TIA
 
F

Frank Kabel

Hi
try something like
=SUMPRODUCT(--(A1:CX1="WT"),--(MOD(COLUMN(A1:CX1)-1,3)=0))
 
F

Frank Kabel

Hi
also see my other post. In this case you may use as alternative:
=SUMPRODUCT(--(F3:U3="/"),--(MOD(COLUMN(F3:U3)-6,3)=0))
 
A

Aladin Akyurek

The data of interest appears to be regularly spaced (every 3d column). Given
the fact that you require a formula that can be copied across, I'd
suggest...

=SUMPRODUCT(COUNTIF(OFFSET($F$3,0,{0,3,6,9,12,15}+COLUMNS($A$3:A3)-1),"/"))

presupposing that A3 is the cell where the first calculated count is
displayed.
 
Top