Count(if(if(..))) not working

[bluesky6688]

I wanted to count the values that matches some criteria. I used the "Count(if(if()))" before and it worked perfectly. These formulae still work in their original cells. Today I tried to make similar ones but they wouldn't work. See example below:

	A	B	C	D	E	F	G
1	ID	group	age	Dist	m1	m2	m3
2	C1	AA	6	50	11	21	31
3	C1	AA	6	100	12	22	32
4	C1	AA	6	150	13	23	33
5	C1	AA	6	200	14	24	34
6	C2	BB	8	50	15	25	35
7	C2	BB	8	100	16	26
8	C2	BB	8	150	17		37
9	C2	BB	8	200		28	38
10	C2	BB	8	250	19	29
11	C2	BB	8	300	20	30	40
12	C2	BB	8	400		31	41
18	C2	BB		7	7	7	7

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

D18 - G18 = COUNT(IF($A$2:$A$17=$A$20,IF($B$2:$B$17=$B$20,G2:G17)))

E18 and F18 should return 6 and G18 should return 5

I tried "Countifs" function but it wouldn't work either:

=countifs($A$2:$A$17,$A$20,$B$2:$B$17,$B$20,G2:G17,<>"")

Thanks for any help and have a great weekend!

 

[shg]

=countifs($A$2:$A$17, $A$20, $B$2:$B$17, $B$20, $G$2:$G$17, "<>")

 

[shahfahad]

What is in cell A20 and B20?

 

[bluesky6688]

=countifs($A$2:$A$17, $A$20, $B$2:$B$17, $B$20, $G$2:$G$17, "<>")

This works. Thank you, shg!

I also made the count(if(if(...))) work by adding another criteria if(G2:G17<>"". I like this formula because I can use it to calculate averages and stdev with just changing one word.

What's the difference between "<>" and <>""? Do they both seem to mean nonspace but just used in different situations?

 

[bluesky6688]

What is in cell A20 and B20?

Should be A18 and B18. They were not updated when I deleted several rows before copying the cells.

 

[shg]

What's the difference between "<>" and <>""?

The first is a string that gets evaluated by the function. The second is an invalid expression.