Countifs on another Column

[gina clemens]

Hi,


I have a large set of data I am working on which contains alot of moisture data. Within my huge set of data there are moisture readings that come from various treatments. I am trying to do averages on a bunch of these different treatments for which I am using an averageifs statment which is working nicely - but... in quite a number of cases there is 1 of, for example 5, of the moisture readings for a particular treatment missing - in which case I want to just have an empty cell as the average of the 4 remaining moistures may not be representative.


So I would like to do something that works excatly like the averageifs but rather than giving me the average it says that eg 4 cells make up the average - so a count. But countifs seems to work differently as I can't tell it to count all the cells in column H that meet meet critera 1 in coulumn A, critera 2 in Coloum B and Critera 3 in Column C.


I hope this makes sense. The idea is that I can eventually tell have a formula saying that if the count in column H doesn't = 5 (or whatever the number maybe for that treatment) then just leave the cell empty rather than giving me the average.

 

[SirJB7]

Hi, gina clemens!


AVERAGEIFS function is equivalent to SUMIFS/COUNTIFS functions, so if you did something like:

=PROMEDIO.SI.CONJUNTO(H2:H17;A2:A17;I1;B2:B17;J1;C2:C17;K1) -----> in english: =AVERAGEIFS(H2:H17,A2:A17,I1,B2:B17,J1,C2:C17,K1)

you can do this:

=SUMAR.SI.CONJUNTO(H2:H17;A2:A17;I1;B2:B17;J1;C2:C17;K1)/CONTAR.SI.CONJUNTO(A2:A17;I1;B2:B17;J1;C2:C17;K1) -----> in english: =SUMIFS(H2:H17,A2:A17,I1,B2:B17,J1,C2:C17,K1)

/COUNTIFS(A2:A17,I1,B2:B17,J1,C2:C17,K1)

and check the condition required for the COUNTIFS expression.


Regards!

 

[gina clemens]

Hi,

Thanks I have tried that but it doesn't give me the answer I am after.

The sumifs formula works how I would like the countifs function to but gives me the sum not the count obviously - but it is the sum of the results in column H.


But the countifs function just looks at criteria range and criteria and gives a count of how many 'TDRS' in this case fit the critera - what I am after is of the tdrs that fit critera 1 and 2 how many having a reading in column H. I really want the formula to work like this =countifs(count_range,Criteria_Range1,Criteria1,Criteria_Range 2,Criteria_2) ... and so on


Thanks.

 

[Colin Legg]

Hi Gina,


So the

COUNTIFS() formula would be like:

[code]=COUNTIFS(A2:A10,"Criteria1",B2:B10,"Criteria2",C2:C10,"Criteria3")


Say you put that formula in cell L2. Then your [code]AVERAGEIFS() formula would be:

[code]=IF(L2=5,AVERAGEIFS(H2:H10,A2:A10,"Criteria1",B2:B10,"Criteria2",C2:C10,"Criteria3"),"")

where H2:H10 are the values you want to average. In other words that formula says: If the conditional count is 5 then take the average of those 5 values, otherwise return an empty string.


Alternatively, since you've already got the conditional count, you could use SUMIFS()[/code] instead of AVERAGEIFS()[/code] and then divide by the count:

=IF(L2=5,SUMIFS(H2:H10,A2:A10,"Criteria1",B2:B10,"Criteria2",C2:C10,"Criteria3&#34/L2,"&#34[/code]