Microsoft Office Forums count or sum with fixed increment
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
01-18-2023, 05:24 PM
 egit Windows 11 Office 2019 Novice Join Date: Jan 2023 Posts: 2
count or sum with fixed increment

Hello,

I have a large table of more than 2000 rows, and 5 columns. The values of these cells are either 1 or 0. For each column I want to be able to count the number of 1s in blocks of 20 rows starting from the first row. Example: For each column count the number of 1s in the first 20 cells (A1:A20), then in cells 21 to 41 (A21:A41), then from 42 to 62 and so on. Any help is appreciated.

Many thanks,
egit
#2
01-18-2023, 06:32 PM
 Guessed Windows 10 Office 2016 Expert Join Date: Mar 2010 Location: Canberra/Melbourne Australia Posts: 3,380

Are you sure this is blocks of 20? The range A21:A41 has 21 cells, as does A42:A62
I will assume all blocks are 20 cells and you want the data compiled in a column starting in the first row.

Put a formula in cell H1 with this value
=SUM(OFFSET(\$A\$1,(ROW()-1)*20,0,20,1))

Then copy this formula down for as many rows as you need.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
#3
01-19-2023, 04:38 AM
 Pecoflyer Windows 10 Office 2021 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,667

An option to avoid the OFFSET volatile function
Code:
`=SUM(INDEX(\$A\$1:\$A\$100,((ROW()-1)*4+1)):INDEX(\$A\$1:\$A\$100,ROW()*4))`
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
#4
01-19-2023, 03:32 PM
 egit Windows 11 Office 2019 Novice Join Date: Jan 2023 Posts: 2

Many thanks, both solutions work.
#5
01-20-2023, 02:17 AM
 Pecoflyer Windows 10 Office 2021 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,667

Please mark thread as solved ( see Thread tools). Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

 Tags count, offset, row

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post wheddingsjr Excel 4 09-01-2020 09:52 PM jayreb31 Project 0 07-03-2019 01:14 PM ketanco Project 1 11-24-2014 03:46 PM Evgeny Project 3 01-03-2013 06:45 AM Joeb Project 3 06-25-2012 04:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:20 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft