Combining countif with more than one parameter and cells with no value

Highlighted
Occasional Contributor

Hi all, I’m hoping you can help please. I am looking to combine two countif cells into one if possible please? I currently have some data, where the first countif counts the number of values in a column outside of a range and whether there is any less than results (which are counted as a zero). The second set of data is a formula which uses the original results with another parameter, and then a countif counts the number of values outside of a second range and any less than results.

I’m trying to combine the two countifs so that it only counts if both sets of data are outside the parameters, but I’m having problems as there is not always results in the second column. Example is attached: C1 – C3 and column E are all formulas, and column G is manual, but that’s the formula I am after.

Thanks for your help.

2 Replies
Highlighted
If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…
Highlighted

@Keggsy 

That could be

=SUMPRODUCT(--(
  ((C8:C22>=$C$6)*(C8:C22<=$C$5)+
   (E8:E22>=$E$6)*(E8:E22<=$E$5))
>0))

for data sets inside values