Counting the number of rows that meet multiple criteria

[D-Angle]

Hello all

I'm a long-time reader of these boards, they have given me a lot of help and tips in the past and kept me in a job on more than one occasion. I'm now stuck on a spreadsheet in which I am trying to analyse a large amount of data using formulas, and none of the suggested solutions I have found seem to work. This is a section of the dataset I am working on, it's a sheet of hourly prices of the FTSE100 Index:

What I would like to do, is count the number of rows that meet the same criteria in multiple cells. For example, I would like to count how many times the index was up at 3pm on a Wednesday, how many times it opened down on a Tuesday etc. I would also like to get more complex results as well, such as how many times the index was, say, up at 3pm when it was down at 10am, which I think would be easier to generate if I could count how many rows met those 2 criteria to start with. Most of my searching suggested DCOUNTA, but nothing seems to give a valid result.

Any suggestions greatly appreciated!

 

[FormR]

Btw you are correct that "TRUE" is actually text.

You can test this by using the following formula pointed at one of your TRUE values:
=TRUE=A1

It should return FALSE if your values are text.

For the example of the formula working below I have replaced "True" with "Text" to avoid ambiguity.

Excel Workbook
	A	B	C	D	E	F
1	text	text	text	text		16
2		text
3			text
4		text
5
6	text
7
8	text		text
9
10
11			text
12
13
14			text
15	text
16
17			text
18	text		text
19
20				text
21				text
22	text
23
24	text		text
25
26				text
Sheet1

 

[Aladin Akyurek]

Hi guys,

I have a similar query but the formulae provided in this forum aren't working for my data. Essentially I want to count the number of rows that contain text 'True' in either of the 4 columns I have. I do not want the formula to count the row twice if that row which has the text 'True' is in column C as well as D.

I am using Excel Microsoft Office Professional 2010. Any help would be appreciated.

Control+shift+enter, not just enter:

=SUM(IF(MMULT((A2:D15=TRUE)+0,TRANSPOSE(COLUMN($A$2:$D$15)^0)),1))

 

[Baseem]

I used the test formula and it worked but when I use the formula below:

=SUMPRODUCT(--((A1:A8="TRUE")+(B2:B8="TRUE")+(C2:C8="TRUE")+(D2:D8="TRUE")>0))
it comes as #N/A

(sorry for some reason I couldn't printscreen and paste onto the forum

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
</tbody>[/TABLE]

 

[Baseem]

Hello Aladin,

The formula worked! Please can you explain how the formula is working so I can apply further. Thank you guys, you are legends in my book

Baseem

 

[ngocdiep1510]

Hi,

Using VBA I came up with the code below (4 columns, 100 rows):

i = 0
For Row = 1 To 100
j = 0
    For col = 1 To 4
        If Cells(Row, col).Value = "TEXT" Then
        j = j + 1
        End If
    Next col
    If j > 0 Then
        i = i + 1
    End If
Next Row


MsgBox ("results:" & i)

Can you guys suggest other alternatives? I'm new to VBA :D

 

[FormR]

I used the test formula and it worked but when I use the formula below:

=SUMPRODUCT(--((A1:A8="TRUE")+(B2:B8="TRUE")+(C2:C8="TRUE")+(D2:D8="TRUE")>0))
it comes as #N/A

Stick with Aladin's but.. either:

=SUMPRODUCT(--((A2:A8="TRUE")+(B2:B8="TRUE")+(C2:C8="TRUE")+(D2:D8="TRUE")>0))

or

=SUMPRODUCT(--((A2:A8=TRUE)+(B2:B8=TRUE)+(C2:C8=TRUE)+(D2:D8=TRUE)>0))

 

[Baseem]

Hi FormR,

Thanks the latter of the two formulae worked as well!btw what is the difference between using the operator + and using * in excel and is this <> mean not equal to

 

[FormR]

the latter of the two formulae worked as well!

Cool although this would indicate that the below statement was not technically true..

Btw you are correct that "TRUE" is actually text.

The plus operator in this instance is used as an OR statement versus the * which is used as an AND statement.

btw what is the difference between using the operator + and using * in excel and is this <> mean not equal to

 

[FormR]

BTW - these can be tricky concepts and everything I know on the subject I learnt from hanging out at this forum de-ciphering solutions posted by the likes of Aladin.

 

[Aladin Akyurek]

Hello Aladin,

The formula worked! Please can you explain how the formula is working so I can apply further. Thank you guys, you are legends in my book

Baseem

Given a part your previous exhibit in A2:D4 for ease of exposition...

[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]TRUE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

We have the following successive evalutions of the formula..

SUM(IF(MMULT(($A$2:$D$4=TRUE)+0,TRANSPOSE(COLUMN($A$2:$D$4)^0)),1))

==> (the semi-colon means: per row)

SUM(IF(MMULT(({FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE}=TRUE)+0,TRANSPOSE(COLUMN($A$2:$D$4)^0)),1))

==> (TRUE=TRUE yields TRUE, otherwise FALSE)

lSUM(IF(MMULT(({FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE})+0,TRANSPOSE(COLUMN($A$2:$D$4)^0)),1))

==> (adding a 0 to a TRUE yields 1; adding a 0 to a FALSE yields 0)

SUM(IF(MMULT({0,0,0,0;1,1,1,1;1,1,0,0},TRANSPOSE(COLUMN($A$2:$D$4)^0)),1))

==> (the column bit)

SUM(IF(MMULT({0,0,0,0;1,1,1,1;1,1,0,0},TRANSPOSE({1,2,3,4}^0)),1))

==> (the column bit raised to the power 0 yields an horizontal identity vector)

SUM(IF(MMULT({0,0,0,0;1,1,1,1;1,1,0,0},TRANSPOSE({1,1,1,1})),1))

==> (transposing the horizontal vector yields a vertical identity vector)

SUM(IF(MMULT({0,0,0,0;1,1,1,1;1,1,0,0},{1;1;1;1}),1))

==> (note that MMULT will multiply a matrix with an appropriate identity vector)

SUM(IF({0;4;2},1))

==> (IF considers a zero value as FALSE, a non-zero value as TRUE)

SUM({FALSE;1;1})

==> (summing the 1's for non-zero values IF returns we get...)

2

which is the desired value.

To recap: We look for equality to TRUE per row, convert the evaluations into 1/0's, and feed them to MMULT as a matrix of which each row gets multiplied with a vertical identity vector. Such fed to an IF allows us to obtain a count. Note that this formula is easily extendible to more rows and columns.

Hope this helps.