Excel 2007 : problem with countif and sumif non continuous cells

[lizsantiago07]

Hi everyone, i need help please... i have this formula =IF(SUM(F10:F114)=0,"",COUNTIF(F10:F114,">=90")) it works for what i want if the cell are continous but i need it to do the same with not continous cells.... i need to do it with cell f11,14,17,20,23,26 and so on until cell 114

is it possible?

[dilipandey]

Hi Lizsantiago07,

What is the difference when cell are continuous or non continuous.. are your referring to blank cells ?

Upload a sample file...


Regards,
DILIPandey

<click on below 'star' if this helps>

[lizsantiago07]

No they are not blank, the other cells have numbers too but i dont want to include them in the formula so if i use f10:f114 it will sum and count everything and what i need is f11,14,17,20,23,26 and so on until cell 114 basically every 3 cells

[dilipandey]

Hi lizsantiago07,

You can use a dynamic name for this kind of range... see the attached file where I have used a dynamic name "data" .. press Ctrl + F3 to see the name and it's definition.. thanks.

dynamic name example.xlsx
Regards,
DILIPandey

<click on below 'star' if this helps>

[lizsantiago07]

thaks for your time dilipandey
I wrote a name for those cells but i need to count the cells if they are more than 90% and it's not working is giving me ####

here is what i used: =COUNTIF(nota1,">90")

[dilipandey]

Remove the quotes from your formula.. and try again. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[lizsantiago07]

nope that didnt work either

[dilipandey]

Okay.. upload the sample workbook having your formula.. also highlight the row and range to be checked. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[NBVC]

This will count every 3rd cell starting from F11 to F114, that is greater than 90.

=SUMPRODUCT((MOD(ROW(F11:F114)-ROW(F11),3)=0)*(F11:F114>90))

[lizsantiago07]

here is the wb but it has another formula cuse the one i tried gives an error...please keep in mind that what i want is to count how many grades (is a rollbook i am a teacher) above 90, between 80-89, 70-79, 60-69 and below 59 i have. i will use additional cell with the same formula to count the different grade ranges.

[lizsantiago07]

NBVC thaks but that doesnt work because if you see my sample wb i only have 2 grades above 90 and when i tried your formula it says i have 29

[NBVC]

A couple of reasons...

1. Your actual numbers are percentages, so you want >=0.90 or >=90%

2. You have formula nulls in many, and nulls are larger than numbers... so get counted.

Either you replace the formulas to return 0 instead of null, eg. =IF(OR(F10="",F$4=""),0,(F10/F$4)) and you can format cells as custom: 0;-0;;@ to hide 0's... or use formula:

=SUMPRODUCT((MOD(ROW(F11:F114)-ROW(F11),3)=0)*((0&F11:F114)+0>=90%))

[lizsantiago07]

Nbvc great! but what if i want to count grades between 80-89?

[NBVC]

=sumproduct((mod(row(f11:f114)-row(f11),3)=0)*((0&f11:f114)+0>=80%)*((0&f11:f114)+0<=89%))

[lizsantiago07]

OMG sorry if i am bothering too much... those formulas work super but when i used it to count the students that have an F (0-59) it counts the cells that are blanks as 0 i tried your formula with a =if(f11:f114="","",(sumproduct((mod(row(f11:f114)-row(f11),3)=0)*((0&f11:f114)+0>=0%)*((0&f11:f114)+0<=59%)) but not luck with this..any suggestions?

[NBVC]

do you need >=0% or just >0%? that would fix it, otherwise add:

=SUMPRODUCT((MOD(ROW(F11:F114)-ROW(F11),3)=0)*(F11:F114<>"")*((0&F11:F114)+0>=0%)*((0&F11:F114)+0<=59%))

[lizsantiago07]

THk you so much!!!!!!!!