Turn off or not include lines in a calculation
I have a spread sheet that has a formula at the bottom to count all cells
that have the work day. This calculation =COUNTIF(B7:O170,"DAY"). This counts all cells that have the word day. I want to exclude Lines 7, 10, 13, 16, 19 and so forth, only have the calculation for say every 3rd line. Is there a way to exclude certain lines in my calculation. Thanks for any help. -- DLAYMANS |
Turn off or not include lines in a calculation
maybe (not tested)
=sumproduct(--(B7:O170="DAY"),--(mod(row(B7:O170)-7,3)=0)) "DLAYMANS" wrote: I have a spread sheet that has a formula at the bottom to count all cells that have the work day. This calculation =COUNTIF(B7:O170,"DAY"). This counts all cells that have the word day. I want to exclude Lines 7, 10, 13, 16, 19 and so forth, only have the calculation for say every 3rd line. Is there a way to exclude certain lines in my calculation. Thanks for any help. -- DLAYMANS |
Turn off or not include lines in a calculation
oops, should be <
=sumproduct(--(B7:O170="DAY"),--(mod(row(B7:O170)-7,3)<0)) "Duke Carey" wrote: maybe (not tested) =sumproduct(--(B7:O170="DAY"),--(mod(row(B7:O170)-7,3)=0)) "DLAYMANS" wrote: I have a spread sheet that has a formula at the bottom to count all cells that have the work day. This calculation =COUNTIF(B7:O170,"DAY"). This counts all cells that have the word day. I want to exclude Lines 7, 10, 13, 16, 19 and so forth, only have the calculation for say every 3rd line. Is there a way to exclude certain lines in my calculation. Thanks for any help. -- DLAYMANS |
Turn off or not include lines in a calculation
Duke,, appreciate your help,, when I put the formula in, I get #value! in the
cell where I have the formula. Can you lead me a little further. What I have is a Spread sheet where we track fellows work record. We have several items that we show the man when he works or is off,, Rest, Off With Permission, Off Sick, ect. At the bottom of the sheet we have it total the items with the same data,, such as sick. Any help would be greatly appreciated. D -- DLAYMANS "Duke Carey" wrote: oops, should be < =sumproduct(--(B7:O170="DAY"),--(mod(row(B7:O170)-7,3)<0)) "Duke Carey" wrote: maybe (not tested) =sumproduct(--(B7:O170="DAY"),--(mod(row(B7:O170)-7,3)=0)) "DLAYMANS" wrote: I have a spread sheet that has a formula at the bottom to count all cells that have the work day. This calculation =COUNTIF(B7:O170,"DAY"). This counts all cells that have the word day. I want to exclude Lines 7, 10, 13, 16, 19 and so forth, only have the calculation for say every 3rd line. Is there a way to exclude certain lines in my calculation. Thanks for any help. -- DLAYMANS |
All times are GMT +1. The time now is 09:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com