Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2023, 05:24 PM
egit egit is offline count or sum with fixed increment Windows 11 count or sum with fixed increment Office 2019
Novice
count or sum with fixed increment
 
Join Date: Jan 2023
Posts: 2
egit is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-18-2023, 06:32 PM
Guessed's Avatar
Guessed Guessed is offline count or sum with fixed increment Windows 10 count or sum with fixed increment Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 01-19-2023, 04:38 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now count or sum with fixed increment Windows 10 count or sum with fixed increment Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 01-19-2023, 03:32 PM
egit egit is offline count or sum with fixed increment Windows 11 count or sum with fixed increment Office 2019
Novice
count or sum with fixed increment
 
Join Date: Jan 2023
Posts: 2
egit is on a distinguished road
Default

Many thanks, both solutions work.
Reply With Quote
  #5  
Old 01-20-2023, 02:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now count or sum with fixed increment Windows 10 count or sum with fixed increment Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Tags
count, offset, row

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
count or sum with fixed increment Why does the total row of my pivot table not tie to the real count using distinct count wheddingsjr Excel 4 09-01-2020 09:52 PM
How to create a Man Hour Resource with fixed durations and fixed Work jayreb31 Project 0 07-03-2019 01:14 PM
count or sum with fixed increment fixed units to fixed duration ketanco Project 1 11-24-2014 03:46 PM
count or sum with fixed increment Fixed duration, fixed units, fixed work, effort driven. How, why and when use it all Evgeny Project 3 01-03-2013 06:45 AM
count or sum with fixed increment How to use fixed duration, fixed work and have resources/units leveled automatically? Joeb Project 3 06-25-2012 04:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:19 AM.


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