Count If/ SumProduct is including blank rows

[Yoder61]

Having trouble, I do not want the blank rows counted that do not have data yet, but the list constantly grows.
I want it to give me a count of people who have a number in column R that is equal to or less then 199 and in Column S equal to or less than 82, but not count rows with no data. It is including all the blank rows in my count right now.

=SUMPRODUCT(--(SIGN(('Tab Name Here'!R3:R65000<=199)+('Tab Name Here'!S3:S65000<=82))))

 

[Jonmo1]

Welcome to the board.

If you have XL2007+ you can use countifs
=COUNTIFS('Tab Name Here'!R3:R65000,"<=199",'Tab Name Here'!S3:S65000,"<=82")

If not, then you need sumproduct
Unfortunately, unlike countif(s) sumproduct considers blank cells to be 0, so you have to add criteria to specify Not blank
=SUMPRODUCT(--('Tab Name Here'!R3:R65000<=199),--('Tab Name Here'!R3:R65000<>""),--('Tab Name Here'!S3:S65000<=82),--('Tab Name Here'!S3:S65000<>""))

 

[harikammula]

Hi,

See if resolves using IF(ISBLANK(criteria),"",your condition

let me know, we look for further answers if doesn't work

 

[Comfy]

Answered

 

[Yoder61]

Thanks that really helped I see what I did wrong! If I want to now take it a step further with same data. compare current scores to previous scores and only give me a count if the scores are lower now, this is what I'm trying but its not working.

=SUMPRODUCT(--('Tab Name Here'!R4:R65001<P4:P65000),--('Tab Name Here'!R4:R65000<>""),--('Tab Name Here'!S4:S65000<Q4:Q65000),--('Tab Name Here'!S4:S65000<>""))

I have old scores in column P & Q (there is 2 scores for each test, one in P and one in Q). Then same test New Scores in columns R & S. I want it to only count the rows with data (no blanks like before) that have lowered there current scores. So count if Column R is less then column P and column S is less then Q.

Example:
Old Score 1 P Old Score 2 Q New Score 1 R New Score 2 S
50 100 49 89

if the new scores in R & S are lower now (lower is better in this case), then count. So we can see how many lowered there scores but don't count blank rows.

 

[Jonmo1]

Assuming R is previous score, S is current score
This would count rows where S is lower than R

=SUMPRODUCT(--('Tab Name Here'!S4:S65000 < > <Q4:q65000),--('tab Name="" Here?!S4:S65000<="">""),--('Tab Name Here'!R4:R65000 < > ""),--('Tab Name Here'!S4:S65000 < 'Tab Name Here'!R4:R65001))</Q4:q65000),--('tab>

 

[Yoder61]

=SUMPRODUCT(--('Encounter Information'!R4:R65001<P4:P65001),--('Encounter Information'!R4:R65001<>""),--('Encounter Information'!S4:S65001<=Q4:Q65001),--('Encounter Information'!S4:S65001<>""))

above formula is what I'm trying but not working.

 

[Yoder61]

I don't think I pasted it correctly, trying to fix now.

 

[Jonmo1]

Put spaces around < and > symbols
Board software tends to treat them as HTML code tags.

 

[Yoder61]

=SUMPRODUCT(--('Tab Name'!R4:R65001 < P4:P65001),--('Tab Name'!R4:R65001 < > ""),--('Tab Name'!S4:S65001 < =Q4:Q65001),--('Tab Name'!S4:S65001 < > ""))