COUNTIF Non=contiguous selection that is greater than cell "x"

[Deus_Ex_Machina]

Hi All,
So I have rows of data where I want to count all numbers that are greater than the value in a fixed cell (M13) however I only want to count the numbers in every second column. I have tried the below however the problem is the values are hard coded so when I add more columns (and I plan to) the whole things goes kaput.

=SUM(COUNTIF(INDIRECT({"A13","C13","E13"}),"<"&M13))

I actually thought this would be an easy function but apparently not.

Any ideas oh wise ones?

 

[oldbrewer]

a macro would do it - can you use macros ?

 

[footoo]

Maybe SUMPRODUCT : https://www.google.com.hk/search?q=....69i57j0l2.29083j0j8&sourceid=chrome&ie=UTF-8

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(MOD(COLUMN(A13:E13)-COLUMN(A13),2)=0),--(A13:E13 < M13))

 

[oldbrewer]

[TABLE="width: 1155"]
<colgroup><col span="12"><col><col span="2"></colgroup><tbody>[TR]
[TD]score1[/TD]
[TD]score2[/TD]
[TD]score3[/TD]
[TD]score4[/TD]
[TD]score5[/TD]
[TD]score6[/TD]
[TD]score7[/TD]
[TD]score8[/TD]
[TD]score9[/TD]
[TD]score10[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]count[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]number of columns[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]number of rows[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]So I have rows of data where I want to count all numbers that are greater than the value in a fixed cell (M13) however I only want to count the numbers in every second column[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the macro that correctly counts 6 is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the number of columns is counted automatically[/TD]
[TD][/TD]
[/TR]
[TR]
[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]as is the number of rows[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]' Macro recorded 03/09/2018 by bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] For j = 2 To Cells(15, 13) Step 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] For k = 2 To Cells(17, 13)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] If Cells(k, j) > Cells(11, 13) Then Count = Count + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(2, 13) = Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/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]

 

[Deus_Ex_Machina]

Thank you @Aladin Akyurek worked an absolute treat. Don't fully understand it but it works lol.

 

[Aladin Akyurek]

Thank you @Aladin Akyurek worked an absolute treat. Don't fully understand it but it works lol.

[Table="width:, class:grid"][tr][td="bgcolor:#C6E0B4"]

3​

[/td][td="bgcolor:#C6E0B4"]

0.2​

[/td][td="bgcolor:#C6E0B4"]

5​

[/td][td="bgcolor:#C6E0B4"]x[/td][td="bgcolor:#C6E0B4"]

1​

[/td][td]GIVEN[/td][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]EVERY SECOND[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]< 4?[/td][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]SUM 1's[/td][/tr]
[/table]

The second row shows the MOD evaluation: It says 1 for every second column, starting with A13, otherwise 0.

The third row shows the A13:E13 < M13 evaluation where M13 = 4 for each cell of A13:E13. If the less than evaluation holds, we get a 1, otherwise 0.

The last row shows the multiplication of the MOD results by the results of the < evaluation. Every pairwise multiplication (what SUMPRODUCT does) returns here either a 1 or a 0. Summing the multiplication results, that is, summing 1's and 0's, gives us the count we are after.


To recap: MOD marks cells as 1 if they are "every second" and the < evaluation marks every cell as 1 if < holds. Multiplying markings pairwise and summing the results of pairwise mutiplications gives us the count.


Hope this helps.