Count every Nth collum

[andes7]

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 .

 

[Jonmo1]

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")

 

[Mackers]

In D, counting all cells from E to Z (change as required), to count any cells that are exactly an "x":

=COUNTIFS(E2:Z2,"x")

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

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

In E:

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

Drag this right for the others

Hope that helps

Mackers

 

[andes7]

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

 

[VBA Geek]

Excel 2010
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3	2	118	105	117	x	10	20	$ 56.00		99	8	60	x	9	77	1
Sheet1

Cell Formulas
Range	Formula
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.

 

[Jonmo1]

These proposed solutions may be more complicated than they need to be.
Please consider my question from post #2 .

Do you have any header values in those columns?
What are they, maybe they can be used in a countifs function.

 

[Mackers]

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))

 

[andes7]

I get the same error with this.

 

[Mackers]

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.

 

[andes7]

Just to clarify the data I have, as I have changed this up a little.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]S[/TD]
[TD]G[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]G[/TD]
[TD]N[/TD]
[TD]S[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]G[/TD]
[TD]N[/TD]
[TD]S[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jim[/TD]
[TD]Smith[/TD]
[TD]Jim Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]s[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Simon[/TD]
[TD]Rose[/TD]
[TD]Simon Rose[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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