Count every Nth collum

A

andes7

New Member
Joined
Jan 29, 2018
Messages
4
Hi, I have a formula that I'm struggling with.

I have 4 columns of data that repeat throughout a spreadsheet.

Column H, I, J and K that contain the following data types, Text, Number, Number and Currency. These 4 columns repeat a number of times, L, M, N, O, and P, Q, R, S, and T, U, V, W an so on.

In column D, E, F and G I wish to sum the columns.

In Column D, I wish to count all the instances of a particular instance of text (If the cell has a "x" in it)
In column E, I wish to sum all of the columns values (I, M, Q U and so on..)
In column F, I wish to sum all of the columns values (J, N, R, V and so on..)
In column G, I wish to sum all of the columns values (K, O, S, W and so on..)

Can anyone assist? This will then repeat for every ROW of values .
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Do you have any header values in those columns?
What are they, maybe they can be used in a countifs function.
=COUNTIFS(H2:W2,"x",H1:W1,"Specified Header Text")
 
Upvote 0
In D, counting all cells from E to Z (change as required), to count any cells that are exactly an "x":

Code: 
=COUNTIFS(E2:Z2,"x")

To count any cells that contain an "x" (like "Excalibur" or "2000x"):

Code: 
=COUNTIFS(E2:Z2,"*x*")

In E:

Code: 
=SUMPRODUCT(I2:Z2*(MOD(COLUMN(I2:Z2)-COLUMN(I2)+1,4)=1))

Drag this right for the others

Hope that helps

Mackers
 
Upvote 0
This kind of works. However, when I populate the data, I get the following errors for the sum of E

Function MULTIPLY parameter 1 expects number values. But "x" is a text and cannot be coerced to a number
 
Upvote 0

Excel 2010
DEFGHIJKLMNOPQRS
32118105117x1020$ 56.0099860x9771
Sheet1
Cell Formulas
RangeFormula
D3{=SUM(N(INDEX(H3:S3,N(IF(1,1+4*(ROW(INDIRECT("1:"&INT(COLUMNS($H$3:$S$3)/4)))-1))))="X"))}
E3{=SUM(INDEX($H$3:$S$3,N(IF(1,COLUMNS($D3:E3)+4*(ROW(INDIRECT("1:"&INT(COLUMNS($H$3:$S$3)/4)))-1)))))}
F3{=SUM(INDEX($H$3:$S$3,N(IF(1,COLUMNS($D3:F3)+4*(ROW(INDIRECT("1:"&INT(COLUMNS($H$3:$S$3)/4)))-1)))))}
G3{=SUM(INDEX($H$3:$S$3,N(IF(1,COLUMNS($D3:G3)+4*(ROW(INDIRECT("1:"&INT(COLUMNS($H$3:$S$3)/4)))-1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
These proposed solutions may be more complicated than they need to be.
Please consider my question from post #2 .
Jonmo1 said:
Do you have any header values in those columns?
What are they, maybe they can be used in a countifs function.
Click to expand...
 
Upvote 0
As an update to my solution, using control+shift+enter:

=SUMPRODUCT(IF(ISNUMBER($F5:$Q5),$F5:$Q5)*(MOD(COLUMN($F5:$Q5)-COLUMN($F5)+1,4)=B$4))
 
Upvote 0
It works for me--are you definitely entering the formula using Control+Shift+Enter rather than just Enter?

If it's still not working then you could try looking at the other two solutions posted, or provide more detail on the error, for example by stepping through the calculation to see where it's going wrong.
 
Upvote 0
Just to clarify the data I have, as I have changed this up a little.

ABCDEFGHIJKLMNOPQ
9
10ASGOPGNSOPGNSO
11JimSmithJim Smithx1402s0420
12SimonRoseSimon Rose
13

<tbody>
</tbody>

So in D11, I want to count all the instances of "x" in H11, M11, R11 and so on.
In E11, I want to count all the instances of "s" in H11, M11, R11 and so on.
In F11 I want to sum up the values in cells I11, N11, S11 etc
in G I want to sum up all the values in cells L11, Q11, V11 etc
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
153
kerm007
K
Z
  • Question
Count All values in every 3rd column
Replies
4
Views
642
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
206
LazyBug
LazyBug
N
  • Question
MATCH two ranges, column by column
Replies
4
Views
173
Fluff
Fluff
A
  • Question
Need to repeat the first row in every csv file created
Replies
1
Views
152
rpaulson
rpaulson

Forum statistics

Threads
1,214,657
Messages
6,120,764
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top