SIMPLE Countifs problem (2007)

slhangen

New Member
Joined
Jun 21, 2012
Messages
41
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")
A
B
C
D
1
MET
>=10
>=20
>=30
2
0
20
20
19
3
1
15
21
30
4
1
10
25
39

<TBODY>
</TBODY>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
those are the correct answers. Because you are asking if all three conditions are true, count range and you only have a single range
 
Upvote 0
Do you perhaps instead mean:


Excel 2007
ABCD
1MET102030
22202019
33152130
43102539
Sheet3
Cell Formulas
RangeFormula
A2=SUMPRODUCT(--(B2:D2>=B$1:D$1))
 
Upvote 0
to get the answers you were expected try

Excel 2003
ABCD
1>=10>=20>=30
22202019
33152130
43102539
5
Sheet3
Cell Formulas
RangeFormula
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")
 
Upvote 0
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
ABCD
1>=10>=20>=30
22202019
33152130
43102539
5

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet3

Worksheet Formulas
CellFormula
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")

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
 
Upvote 0
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")

blah
y
blah
blah
x
blah
x
y
blah
y

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
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")

blah
y
blah
blah
x
blah
x
y
blah
y

<TBODY>
</TBODY>
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top