Count red numbers only in row

James006

Well-known Member

Joined

Apr 4, 2009

Messages

4,750

Office Version

1. 2016

Platform

1. Windows

• Mar 5, 2017

• #2

Hello,

The best solution is to use the formula you have designed for conditional formatting ...

HTH

 

Upvote 0

S

stkin

Board Regular

Joined

Dec 18, 2013

Messages

53

• Mar 5, 2017

• #3

I'm sorry I don't understand, here is my conditional formatting formula: =c7=min(c7:c106) then I just formatted the text for red in color. this is for each column then I need to go across each row and count the red numbers

thanks

 

Last edited: Mar 5, 2017

Upvote 0

M

Marcelo Branco

MrExcel MVP

Joined

Aug 23, 2010

Messages

17,103

Office Version

1. 2021
2. 2010

Platform

1. Windows

• Mar 5, 2017

• #4

See if this example helps

	A​	B​	C​	D​	E​
1​	Header1​	Header2​	Header3​		Result​
2​	4​	4​	10​		5​
3​	5​	2​	1​
4​	10​	10​	1​
5​	8​	2​	7​
6​	5​	7​	5​

Formula in E2
=SUMPRODUCT(--(A2:C6=SUBTOTAL(5,OFFSET(A2:C6,0,COLUMN(A2:C6)-COLUMN(A2),,1))))

M.

 

Upvote 0

M

Marcelo Branco

MrExcel MVP

Joined

Aug 23, 2010

Messages

17,103

Office Version

1. 2021
2. 2010

Platform

1. Windows

• Mar 5, 2017

• #5

Or if you need the count for every row...

	A​	B​	C​	D​	E​
1​	Header1​	Header2​	Header3​		Count Mins​
2​	4​	4​	10​		1​
3​	5​	2​	1​		2​
4​	10​	10​	1​		1​
5​	8​	2​	7​		1​
6​	5​	7​	5​		0​

Formula in E2 copied down
=SUMPRODUCT(--(A2:C2=SUBTOTAL(5,OFFSET(A$2:C$6,0,COLUMN(A$2:C$6)-COLUMN(A$2),,1))))

M.

 

Upvote 0

S

stkin

Board Regular

Joined

Dec 18, 2013

Messages

53

• Mar 7, 2017

• #6

When I use this I get in column min 18, does the formula need to be different if I have 18 columns then a total column. The total for min could be anywhere from 0 to 18.

Thanks

 

Upvote 0

S

stkin

Board Regular

Joined

Dec 18, 2013

Messages

53

• Mar 7, 2017

• #7

When I use this I get in column min 18, does the formula need to be different if I have 18 columns then a total column. The total for min could be anywhere from 0 to 18. Also I need to count each column with the same formula and it works for that, just not when I do rows. My conditional formatting formula: =c7=min(c7:c106) which I use to turn the lowest number in each column RED, so that's why some rows will have many red numbers. I hope this helps to explain it better. Thanks so much for your help so far, it has been a great help.


Thanks Again

 

Last edited: Mar 7, 2017

Upvote 0

M

Marcelo Branco

MrExcel MVP

Joined

Aug 23, 2010

Messages

17,103

Office Version

1. 2021
2. 2010

Platform

1. Windows

• Mar 7, 2017

• #8

Where are your data? How many columns?
Tell us the exact location of your data.

M.

 

Upvote 0

James006

Well-known Member

Joined

Apr 4, 2009

Messages

4,750

Office Version

1. 2016

Platform

1. Windows

• Mar 7, 2017

• #9

Hello,

Whenever you are using the Subtotal() function ...the argument 5 means Min() ...

HTH

 

Upvote 0

S

stkin

Board Regular

Joined

Dec 18, 2013

Messages

53

• Mar 7, 2017

• #10

Thanks

 

Upvote 0