SIMPLE Countifs problem (2007)

slhangen

New Member
Joined
Jun 21, 2012
Messages
36
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>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Do you perhaps instead mean:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #FF0000;background-color: #FFFFFF;;">MET</td><td style="color: #574123;background-color: #FFFFFF;;">10</td><td style="color: #574123;background-color: #FFFFFF;;">20</td><td style="color: #574123;background-color: #FFFFFF;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="color: #574123;background-color: #FFFFFF;;">20</td><td style="color: #574123;background-color: #FFFFFF;;">20</td><td style="color: #574123;background-color: #FFFFFF;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="color: #574123;background-color: #FFFFFF;;">15</td><td style="color: #574123;background-color: #FFFFFF;;">21</td><td style="color: #574123;background-color: #FFFFFF;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="color: #574123;background-color: #FFFFFF;;">10</td><td style="color: #574123;background-color: #FFFFFF;;">25</td><td style="color: #574123;background-color: #FFFFFF;;">39</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B2:D2>=B$1:D$1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
to get the answers you were expected try
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="color: #333333;background-color: #FFFFFF;;">>=10</td><td style="color: #333333;background-color: #FFFFFF;;">>=20</td><td style="color: #333333;background-color: #FFFFFF;;">>=30</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="color: #333333;background-color: #FFFFFF;;">20</td><td style="color: #333333;background-color: #FFFFFF;;">20</td><td style="color: #333333;background-color: #FFFFFF;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="color: #333333;background-color: #FFFFFF;;">15</td><td style="color: #333333;background-color: #FFFFFF;;">21</td><td style="color: #333333;background-color: #FFFFFF;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="color: #333333;background-color: #FFFFFF;;">10</td><td style="color: #333333;background-color: #FFFFFF;;">25</td><td style="color: #333333;background-color: #FFFFFF;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=COUNTIF(<font color="Blue">B2,">=10"</font>)+COUNTIF(<font color="Blue">C2,">=20"</font>)+COUNTIF(<font color="Blue">D2,">=30"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=COUNTIF(<font color="Blue">B3,">=10"</font>)+COUNTIF(<font color="Blue">C3,">=20"</font>)+COUNTIF(<font color="Blue">D3,">=30"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">=COUNTIF(<font color="Blue">B4,">=10"</font>)+COUNTIF(<font color="Blue">C4,">=20"</font>)+COUNTIF(<font color="Blue">D4,">=30"</font>)</td></tr></tbody></table></td></tr></table><br />
 

slhangen

New Member
Joined
Jun 21, 2012
Messages
36

ADVERTISEMENT

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>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

slhangen

New Member
Joined
Jun 21, 2012
Messages
36
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,968
Messages
5,599,097
Members
414,285
Latest member
excela2z

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
Top