Counting rows where all data is populated

[datadummy]

I am trying to write a Countif formula that reads as follows =COUNTIFS(Data!C:C,"Heart Center",Data!A:A,">06/30/2017",Data!A:A,"<08/01/2017",Data!D:D,">3000",Data!D:D,"<3999") but I now need to incorporate into the formula where it would only count if the entire row is populated, columns E-K could potentially be blank, if ones blank they'd all be blank and shouldn't be counted.

 

[inactiveuserps07]

@datadummy, it seems to me you'd just add the bit in red below:


=COUNTIFS(Data!C:C,"Heart Center"... ... ... Data!D:D,"<3999",Data!E:E,"<#")


However, as always, I'd recommend limiting your ranges (e.g., Data!A:A to Data!A2:A1000, etc.) for the sake of performance/speed.

 

[datadummy]

I gave it a shot just adding that red part to the end and it essentially broke the formula returning #VALUE!.

 

[inactiveuserps07]

@datadummy,

I misread what you wanted. We'll need the opposite of that red part ('greater than' not 'less than'):

Data!E:E,">#"

Give that a try.

 

[datadummy]

@ErikTyler
I tried it again and am still getting #VALUE!. Here is the current formula, =COUNTIFS(Data!D:D,"Heart Center",Data!B2:B100,">06/30/2017",Data!B2:B100,"<08/01/2017",Data!D:D,">3000",Data!D:D,"<3999",Data!E:E,">#"). Not sure why its not working.

 

[inactiveuserps07]

@datadummy, if something WERE to be entered into a cell in Column E, what type/format of information would it be? Are the cells in that column formatted as a specific type? My suggested addition assumes text string, which it shouldn't.

First try replacing with this:

Data!E:E,"<>"&""

 

[Aladin Akyurek]

@datadummy

Control+shift+enter, not just enter:

=SUM(IF(1-(data!$E$2:$K$3=""),IF(data!$A$2:$A$3>"6/30/2017"+0,IF(data!$A$2:$A$3<"8/01/2017"+0,IF(data!$C$2:$C$3="heart center",IF(data!$D$2:$D$3 > 3000,IF(data!$D$2:$D$3 < 3999,1)))))))

Adjust the ranges to suit. Do not reference whole columns though for reasons of efficiency.

 

[datadummy]

@datadummy, if something WERE to be entered into a cell in Column E, what type/format of information would it be? Are the cells in that column formatted as a specific type? My suggested addition assumes text string, which it shouldn't.

First try replacing with this:

Data!E:E,"<>"&""

The data in that column are numerical values.

 

[Aladin Akyurek]

The data in that column are numerical values.

Why don't you try post #7?