 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007Skipping 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).  Signature- Zilla (Remove XSPAM) I found this... http://www.ozgrid.com/forum/archive/index.php?t-26443.html -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). One way: replace =COUNTIF(A1:A1000,"Y") with =SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y")) > 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). Thanks! > One way: > [quoted text clipped - 11 lines] > > (every 3rd cell, n=3), or maybe A1, A3, A5, etc. > > (every 2nd cell, n=2). 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. > One way: > [quoted text clipped - 11 lines] > > (every 3rd cell, n=3), or maybe A1, A3, A5, etc. > > (every 2nd cell, n=2). 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)) > I tested this just by putting all 1's in A1:A10, > and put your SUMPRODUCT formula in B1. [quoted text clipped - 12 lines] > > > > =SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y")) 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... > Not sure why you used 1's. If you want to use 1's change the SUMPRODUCT > Formula to [quoted text clipped - 17 lines] > > > > > > =SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y")) > 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. > 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 [quoted text clipped - 21 lines] > > > > > > > > =SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y")) Yes, thanks, I missed the mod(x,1). What do the "--" mean in your formula? > > if I count every cell (mod 1) > [quoted text clipped - 34 lines] > > > > > > > > > > with =SUMPRODUCT(--(MOD(ROW(INDIRECT("1:1000")),3)=1),--(A1:A1000="Y")) See http://www.mcgimpsey.com/excel/doubleneg.html > Yes, thanks, I missed the mod(x,1). What do the "--" mean in > your formula? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.