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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
Hello titekinou, welcome to MrExcel,

One approach is to SUMPRODUCT, e.g.

=SUMPRODUCT((A2:A9=2)*(B2:B9=1))
 
Upvote 0
Hello and welcome to MrExcel.

Try

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

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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