SIMPLE Countifs problem (2007)

[slhangen]

In EXCEL 2007, Been using COUNTIFS for awhile, now all the sudden I can't get it to work. Please see the below table and let me know if I am going crazy. Because I can't figure out what the @#%$ is giving me these MET #'s.
Shouldn't
A2=2, A3=3, A4=3

A2=COUNTIFS(B2,">=10",C2,">=20",D2,">=30")
A3=COUNTIFS(B3,">=10",C3,">=20",D3,">=30")
A4=COUNTIFS(B4,">=10",C4,">=20",D4,">=30")
[TABLE="width: 192"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent"]B
[/TD]
[TD="width: 64, bgcolor: transparent"]C
[/TD]
[TD="width: 64, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1
[/TD]
[TD="width: 64, bgcolor: transparent"]MET
[/TD]
[TD="width: 64, bgcolor: transparent"]>=10
[/TD]
[TD="width: 64, bgcolor: transparent"]>=20
[/TD]
[TD="width: 64, bgcolor: transparent"]>=30
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"]20
[/TD]
[TD="class: xl63, bgcolor: transparent"]20
[/TD]
[TD="class: xl63, bgcolor: transparent"]19
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"]15
[/TD]
[TD="class: xl63, bgcolor: transparent"]21
[/TD]
[TD="class: xl63, bgcolor: transparent"]30
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent"]25
[/TD]
[TD="bgcolor: transparent"]39
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[texasalynn]

those are the correct answers. Because you are asking if all three conditions are true, count range and you only have a single range

 

[Firefly2012]

Do you perhaps instead mean:

Excel 2007
	A	B	C	D
1	MET	10	20	30
2	2	20	20	19
3	3	15	21	30
4	3	10	25	39
Sheet3

Cell Formulas
Range	Formula
A2	=SUMPRODUCT(--(B2:D2>=B$1:D$1))

 

[texasalynn]

to get the answers you were expected try

Excel 2003
	A	B	C	D
1		>=10	>=20	>=30
2	2	20	20	19
3	3	15	21	30
4	3	10	25	39
5
Sheet3

Cell Formulas
Range	Formula
A2	=COUNTIF(B2,">=10")+COUNTIF(C2,">=20")+COUNTIF(D2,">=30")
A3	=COUNTIF(B3,">=10")+COUNTIF(C3,">=20")+COUNTIF(D3,">=30")
A4	=COUNTIF(B4,">=10")+COUNTIF(C4,">=20")+COUNTIF(D4,">=30")

 

[slhangen]

Thanks, that worked. Not sure whythough.

Shouldn't the COUNTIFS formula in 2007 add 1 to the count each time a criteria is met?

to get the answers you were expected try
Excel 2003

	A	B	C	D

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffffff"]>=10[/TD]
[TD="bgcolor: #ffffff"]>=20[/TD]
[TD="bgcolor: #ffffff"]>=30[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #ffffff"]20[/TD]
[TD="bgcolor: #ffffff"]20[/TD]
[TD="bgcolor: #ffffff"]19[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffffff"]15[/TD]
[TD="bgcolor: #ffffff"]21[/TD]
[TD="bgcolor: #ffffff"]30[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffffff"]10[/TD]
[TD="bgcolor: #ffffff"]25[/TD]
[TD="bgcolor: #ffffff"]39[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet3​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]A2[/TH]
[TD="align: left"]=COUNTIF(B2,">=10")+COUNTIF(C2,">=20")+COUNTIF(D2,">=30")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]A3[/TH]
[TD="align: left"]=COUNTIF(B3,">=10")+COUNTIF(C3,">=20")+COUNTIF(D3,">=30")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]A4[/TH]
[TD="align: left"]=COUNTIF(B4,">=10")+COUNTIF(C4,">=20")+COUNTIF(D4,">=30")[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Jonmo1]

Thanks, that worked. Not sure whythough.

Shouldn't the COUNTIFS formula in 2007 add 1 to the count each time a criteria is met?

No.
It counts 1 each time ALL criteria are met.

It's meant for a an array count..

Count each time a cell in A1:A5 = "x" AND a cell in B1:B5 = "y"
Below table would result in 1 (only 1 row contains x in A and y in B
=COUNTIFS(A1:A5,"x",B1:B5,"y")

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]blah
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]blah
[/TD]
[TD]blah
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]blah
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]blah
[/TD]
[TD]y
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[slhangen]

Aww. Thanks so much. Had a huge project due and this was my last hang-up. Got it done with the ehlp of this great and mighty board.

thanks again

No.
It counts 1 each time ALL criteria are met.

It's meant for a an array count..

Count each time a cell in A1:A5 = "x" AND a cell in B1:B5 = "y"
Below table would result in 1 (only 1 row contains x in A and y in B
=COUNTIFS(A1:A5,"x",B1:B5,"y")

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]blah
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]blah
[/TD]
[TD]blah
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]blah
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]y
[/TD]
[/TR]
[TR]
[TD]blah
[/TD]
[TD]y
[/TD]
[/TR]
</TBODY>[/TABLE]