Skipping cells for SUMIF or COUNTIF functions??
 

Anyway to use the above functions to, say,
work every other nth cell in a row or column?
Say I just want to process A1, A4, A7, etc.
(every 3rd cell, n=3), or maybe A1, A3, A5, etc.
(every 2nd cell, n=2).

--
- Zilla
(Remove XSPAM)

Skipping cells for SUMIF or COUNTIF functions??
 

I found this...

http://www.ozgrid.com/forum/archive/...p?t-26443.html

-Zilla

"Zilla" wrote in message
...
Anyway to use the above functions to, say,
work every other nth cell in a row or column?
Say I just want to process A1, A4, A7, etc.
(every 3rd cell, n=3), or maybe A1, A3, A5, etc.
(every 2nd cell, n=2).

--
- Zilla
(Remove XSPAM)

Skipping cells for SUMIF or COUNTIF functions??
 

One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))



In article ,
"Zilla" wrote:

Anyway to use the above functions to, say,
work every other nth cell in a row or column?
Say I just want to process A1, A4, A7, etc.
(every 3rd cell, n=3), or maybe A1, A3, A5, etc.
(every 2nd cell, n=2).

Skipping cells for SUMIF or COUNTIF functions??
 

Thanks!

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))



In article ,
"Zilla" wrote:

Anyway to use the above functions to, say,
work every other nth cell in a row or column?
Say I just want to process A1, A4, A7, etc.
(every 3rd cell, n=3), or maybe A1, A3, A5, etc.
(every 2nd cell, n=2).

Skipping cells for SUMIF or COUNTIF functions??
 

I tested this just by putting all 1's in A1:A10,
and put your SUMPRODUCT formula in B1.

I expected an answer of 4, counting every
3rd cell starting with A1, correct? Instead
I get 0.

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))



In article ,
"Zilla" wrote:

Anyway to use the above functions to, say,
work every other nth cell in a row or column?
Say I just want to process A1, A4, A7, etc.
(every 3rd cell, n=3), or maybe A1, A3, A5, etc.
(every 2nd cell, n=2).

Skipping cells for SUMIF or COUNTIF functions??
 

Not sure why you used 1's. If you want to use 1's change the SUMPRODUCT
Formula to

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000=1))


In article ,
"Zilla" wrote:

I tested this just by putting all 1's in A1:A10,
and put your SUMPRODUCT formula in B1.

I expected an answer of 4, counting every
3rd cell starting with A1, correct? Instead
I get 0.

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))

Skipping cells for SUMIF or COUNTIF functions??
 

No, I just put the number 1 in A1:A10, just to test your formula.
IOW, if I count every cell (mod 1), your formula should give me
10; if I count every 2nd cell (mod 2), I expect 5, etc...

"JE McGimpsey" wrote in message
...
Not sure why you used 1's. If you want to use 1's change the SUMPRODUCT
Formula to

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000=1))


In article ,
"Zilla" wrote:

I tested this just by putting all 1's in A1:A10,
and put your SUMPRODUCT formula in B1.

I expected an answer of 4, counting every
3rd cell starting with A1, correct? Instead
I get 0.

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))

Skipping cells for SUMIF or COUNTIF functions??
 

if I count every cell (mod 1)

No.

When x is an integer, MOD(x, 1) = 0 in all cases, so your result will
always be zero.

if I count every 2nd cell (mod 2), I expect 5

Yes, exactly!

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:10")),2)=1),--(A1:A10=1))

returns 5 when A1:A10 contain 1.


In article ,
"Zilla" wrote:

No, I just put the number 1 in A1:A10, just to test your formula.
IOW, if I count every cell (mod 1), your formula should give me
10; if I count every 2nd cell (mod 2), I expect 5, etc...

"JE McGimpsey" wrote in message
...
Not sure why you used 1's. If you want to use 1's change the SUMPRODUCT
Formula to

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000=1))


In article ,
"Zilla" wrote:

I tested this just by putting all 1's in A1:A10,
and put your SUMPRODUCT formula in B1.

I expected an answer of 4, counting every
3rd cell starting with A1, correct? Instead
I get 0.

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))

Skipping cells for SUMIF or COUNTIF functions??
 

Yes, thanks, I missed the mod(x,1). What do the "--" mean in
your formula?

"JE McGimpsey" wrote in message
...
if I count every cell (mod 1)

No.

When x is an integer, MOD(x, 1) = 0 in all cases, so your result will
always be zero.

if I count every 2nd cell (mod 2), I expect 5

Yes, exactly!

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:10")),2)=1),--(A1:A10=1))

returns 5 when A1:A10 contain 1.


In article ,
"Zilla" wrote:

No, I just put the number 1 in A1:A10, just to test your formula.
IOW, if I count every cell (mod 1), your formula should give me
10; if I count every 2nd cell (mod 2), I expect 5, etc...

"JE McGimpsey" wrote in message
...
Not sure why you used 1's. If you want to use 1's change the
SUMPRODUCT
Formula to

=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000=1))


In article ,
"Zilla" wrote:

I tested this just by putting all 1's in A1:A10,
and put your SUMPRODUCT formula in B1.

I expected an answer of 4, counting every
3rd cell starting with A1, correct? Instead
I get 0.

"JE McGimpsey" wrote in message
...
One way:

replace

=COUNTIF(A1:A1000,"Y")

with


=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y"))

Skipping cells for SUMIF or COUNTIF functions??
 

See

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
"Zilla" wrote:

Yes, thanks, I missed the mod(x,1). What do the "--" mean in
your formula?