If/Countif nested question

[Supe]

Fighting with Excel (which I use VERY rarely).  I want to have a cell return a "yes" value when a countif function returns a value >4, without having to have a separate "helper" column.

i.e. - I want cell E4 to give me a "Yes" when the number of cells in H4:R4 containing any value >0 is greater than 4.

Actual application to help visualize - E4 is my "championship eligible" column.  H4:R4 is the number of points a driver receives if they drive in an event, which will always be at least 1 point if they show up.  They have to drive at least 5 events to be eligible for a championship, regardless of points received.

Any help would be much appreciated - for some reason, whenever I try nesting the countif into the if statement, it's giving me a "yes" as long as there was any value >0 in any column, effectively ignoring the count function.

 

[Flyer_PE]

Try this:

=IF(COUNTIF(H4:R4,">0")>4,"Yes","No")

 

[Master slacker]

=IF(      COUNTIF(h4:r4,">0")>4,  "yes","no")

edit: dammit flyer

 

[mudpuppy]

^I came up with the same as them, which appears to work.

 

[Supe]

Thanks guys, I thought I had tried that, but I must have either added an extra set of quotes in there or it was thrown off by omitting a false result.  This version worked.

 

[Supe]

I do have one more if y'all are feeling frisky:

Any way to make the LARGE function count a blank space as a zero?  

=SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})) works to tally up the top 9 values in the range, but I get a #num! error if any of the cell values are blank.  For ease of data entry/visual format, I'd like to be able to leave those cells blank in lieu of entering 0's.

 

[Flyer_PE]

I do have one more if y'all are feeling frisky:

Any way to make the LARGE function count a blank space as a zero?  

=SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})) works to tally up the top 9 values in the range, but I get a #num! error if any of the cell values are blank.  For ease of data entry/visual format, I'd like to be able to leave those cells blank in lieu of entering 0's.

2 questions:

1.  Are you sure the cell is blank/null and doesn't have a space hiding in it?

2.  Do you actually have nine numbers in the range for it to add?  (If you ask for the 9th largest number and there are only 8 numbers in the range, you will get '#NUM!')

 

[Supe]

2 questions:

1.  Are you sure the cell is blank/null and doesn't have a space hiding in it?

2.  Do you actually have nine numbers in the range for it to add?  (If you ask for the 9th largest number and there are only 8 numbers in the range, you will get '#NUM!')

1. Yes

2. No, which is why I'd like it to treat a blank as a zero, without actually having the zero.  I recognize that it will get the error without anything in the cell, that's what I'm trying to find a workaround for.  I'd also like to do it without having to assign the row as a list, because the rows get shuffled/updated frequently.

 

[Flyer_PE]

Ok.  The error you are getting is being caused by asking for the 9th largest number in a list of 8 numbers or less.  I don't know of an elegant way to get-r-done using only formulas.

 

[Supe]

Ok.  The error you are getting is being caused by asking for the 9th largest number in a list of 8 numbers or less.  I don't know of an elegant way to get-r-done using only formulas.

Thanks, that's what I had feared.  I will likely just use a conditional format for cells that equal 0 to make them stand out less visually.

 

[Audi Driver P.E.]

I do have one more if y'all are feeling frisky:

Any way to make the LARGE function count a blank space as a zero?  

=SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})) works to tally up the top 9 values in the range, but I get a #num! error if any of the cell values are blank.  For ease of data entry/visual format, I'd like to be able to leave those cells blank in lieu of entering 0's.

Just have to add in the conditional. Try using the SUMIF and ISBLANK functions.

 

[Supe]

I don't think that will work with the LARGE function, since that's looking to return a specified number of values (even if those values are zero).  Since ISBLANK just returns a true/false, I can't visualize how you could set a conditional statement to return a lesser number of values than what is specified in the LARGE array.

 

[Audi Driver P.E.]

I don't think that will work with the LARGE function, since that's looking to return a specified number of values (even if those values are zero).  Since ISBLANK just returns a true/false, I can't visualize how you could set a conditional statement to return a lesser number of values than what is specified in the LARGE array.

Good point. I wrestled trying to get it to work for a while and could not. Might be doable with a series of "OR"  in the conditional?  Dunno.

 

[Flyer_PE]

The problem is that you have to have two "if/Then" arguments running in parallel.  Pretty easy to do in programming space, not so much in a formula.

 

[BamaStrucPESE]

How about =IF(COUNT(J4:T4)>8,SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})),SUM(J4:T4)). If the count is less than 9, you just need to sum everything.

 

[Flyer_PE]

How about =IF(COUNT(J4:T4)>8,SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})),SUM(J4:T4)). If the count is less than 9, you just need to sum everything.

Nice!

 

[Audi Driver P.E.]

How about =IF(COUNT(J4:T4)>8,SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})),SUM(J4:T4)). If the count is less than 9, you just need to sum everything.

That works really well. Nice!

 

[Supe]

How about =IF(COUNT(J4:T4)>8,SUM(LARGE(J4:T4,{1,2,3,4,5,6,7,8,9})),SUM(J4:T4)). If the count is less than 9, you just need to sum everything.