Problem with COUNT IF

titekinou

New Member
Joined
Jul 15, 2009
Messages
19
Hi!
Here is my problem =
I have a huge worksheet, with many columns ands hundreds of rows.
Some of the columns are basically the answers to a survey, meaning that for each row, the value is a nomber between 1 and 5.
In order to do a few charts and tabs with them, i need to count the number of 4, the number of 5 and so on.
For now, no problem, I used a COUNTIF and it worked well.
The thing is that i also want to be able to count them in only parts of the rows. for instance, number of 4 in july, or number of 3 for this region..
Could you help me with this? I tried
=COUNT(IF(A2:A9=2;IF(B2:B9=1;1;0);0))
its just an exemple where the region number would be 2 and the number im looking for is 1, but its not working.
I thought of subtotals, but i cant find the way to do a countif inside..
could you help me please? Thanks for your ideas!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
If you've got multiple criteria, you need SUMPRODUCT

Try:

=SUMPRODUCT(--(A2:A9=2),--(B2:B9=1))

PS the -- part just turns true/false into 1/0
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello titekinou, welcome to MrExcel,

One approach is to SUMPRODUCT, e.g.

=SUMPRODUCT((A2:A9=2)*(B2:B9=1))
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Try

=SUMPRODUCT(--(A2:A9=2),--(B2:B9=1))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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