| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT and OFFSET
I have a range of Data: F8:CT8 I need to count the 1st column in this data range with there being 3 columns. I just need to count if there is data in the cell excluding zeros. In other words F8 F9 F10 F11 F12 F13 12 17 I need to know if there is data in f8 and f11 and so forthe... Expect result is 2 Thanks in advance. Regards, EP -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=541381 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and OFFSET
Hi!
Try this: =SUMPRODUCT(--(MOD(ROW(F8:F100),3)=2),--(F8:F100<0),--(F8:F100<"")) Adjust for the end of the range. If you might insert new rows before the start of the range: =SUMPRODUCT(--(MOD(ROW(F8:F100)-ROW(F8),3)=0),--(F8:F100<0),--(F8:F100<"")) Biff "edwardpestian" wrote in message news:edwardpestian.27o6yo_1147396205.3949@excelfor um-nospam.com... I have a range of Data: F8:CT8 I need to count the 1st column in this data range with there being 3 columns. I just need to count if there is data in the cell excluding zeros. In other words F8 F9 F10 F11 F12 F13 12 17 I need to know if there is data in f8 and f11 and so forthe... Expect result is 2 Thanks in advance. Regards, EP -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=541381 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and OFFSET
Changed ROW to COLUMN, and the Range to F8:CT8, and it worked like a charm. Thanks for the help. Regards -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=541381 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and OFFSET
You're welcome. Thanks for the feedback!
Biff "edwardpestian" wrote in message news:edwardpestian.27oobm_1147418701.949@excelforu m-nospam.com... Changed ROW to COLUMN, and the Range to F8:CT8, and it worked like a charm. Thanks for the help. Regards -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=541381 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OFFSET to include blank cells | Excel Worksheet Functions | |||
| Using the OFFSET and COUNT functions to get a moving total | Excel Discussion (Misc queries) | |||
| Offset, Dynamic range, Countif | Excel Discussion (Misc queries) | |||
| Sum Count of Numeric Criterion | Excel Worksheet Functions | |||
| Problems copying cells using offset and counta | Excel Discussion (Misc queries) | |||
Linear Mode
