Skipping cells for SUMIF or COUNTIF functions??

[Zilla]

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]

I found this...

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

-Zilla

 

[JE McGimpsey]

One way:

replace

=COUNTIF(A1:A1000,"Y")

with

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

 

[Zilla]

Thanks!

One way:

replace

=COUNTIF(A1:A1000,"Y")

with

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

 

[Zilla]

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]

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))

 

[Zilla]

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]

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.

 

[Zilla]

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

 

[JE McGimpsey]

See

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