Countif with matrix

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Mar 11, 2014

• #2

Hi,

I imagine there's a non-array solution as well, but this array formula** should work:

=SUM(0+(MMULT(0+(A1:C7>0),TRANSPOSE(COLUMN(A1:C7))^0)>0))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

Upvote 0

L

lexxie2013

Board Regular

Joined

Nov 28, 2013

Messages

225

• Mar 11, 2014

• #3

Hi, a non-array solution (although not the simplest I fear) :

=SUMPRODUCT(NOT(B2:B8)*NOT(C2:C8)*NOT(D2:D8)*NOT(E2:E8)*NOT(F2:F8)*NOT(G2:G8)*NOT(H2:H8))

 

Upvote 0

L

lexxie2013

Board Regular

Joined

Nov 28, 2013

Messages

225

• Mar 11, 2014

• #4

Oops, wrote that incorrectly :

=SUMPRODUCT(NOT(B2:B8)*NOT(C2:C8)*NOT(D2:D8))

Apologies.

 

Upvote 0

R

Rubsie

New Member

Joined

Feb 16, 2009

Messages

7

• Mar 11, 2014

• #5

Hi XOR LX,

Thank you, works great. Now, if I'd like to check whether values in row x > 0, row y > 1 and row z > 2, MMULT can no longer be used? What solution could be used in that case? (I used values of 0 and 1 for ease of explanation but now I realise it makes the solution for my problem different). :s

Thank you.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 11, 2014

• #6

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B2:D8>0,ROW(B2:D8)),ROW(B2:D8)),1))

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 11, 2014

• #7

Rubsie said:

...Now, if I'd like to check whether values in row x > 0, row y > 1 and row z > 2, MMULT can no longer be used? What solution could be used in that case? (I used values of 0 and 1 for ease of explanation but now I realise it makes the solution for my problem different)...

Click to expand...

An exhibit for clarification along with the desired result would be helpful. Guessing from he short specs:

=SUM(IF(FREQUENCY(IF((B2:B8>0),IF(C2:C8>1,IF(D2:D8>2,ROW(A2:A8)))),ROW(A2:A8)),1))

which must be confirmed with control+shift+enter.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Mar 11, 2014

• #8

Just change to:

=SUM(0+(MMULT(0+(A1:C7>{0,1,2}),TRANSPOSE(COLUMN(A1:C7))^0)>2))

Regards

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Mar 11, 2014

• #9

Try this:

Code:

Use Ctrl+Shift+Enter to enter the formula

=COUNT(1/MMULT(-($B$2:$D$8>{0,1,2}),TRANSPOSE(COLUMN($B$2:$D$2))))

Markmzz

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Mar 11, 2014

• #10

@markmzz

This doesn't work. It will include any row containing non-zero values e.g. {1,1,1}, whether they meet the criteria of >0, >1, >2 respectively or not.

Regards

 

Upvote 0