COUNT A CORRESPONDING CELL IF A CONDITION IS MET

[Sandizet]

I am trying to countif - with two columns of information. if the values in
column A = certain criteria, i want to count the number of times a value
appears in Column B - not all values in column A correspond to column b
values. So if I want to see how many females received a "5", I need to see
how many "female" in column A have a corresponding value of "5" in column B.
(not all females have a "5", values can be 1 through 5)

 

[Max]

For multi-criteria counts,
you could use sumproduct, something like this
In say, E2: =SUMPRODUCT((A2:A10="female")*(B2:B10=5))

Then there's also the Pivot table Option,
which is fast & easy to set up to give you all the analysis stuff at one go

Assume your col headers in A1:B1 are : Gender, Val1
Select any cell in the table, click Data > Pivot table
Click Next > Next
In step 3, click Layout, then:
drag n drop Gender into the ROW area
drag n drop Val1 into the COLUMN area
drag n drop Val1 into the DATA area
(double click on it, change it to summarize by: COUNT)
Click OK > Finish. That's it.

Hop over to the pivot sheet (just to the left) for the quick analysis results:
The full listing of all unique Genders will appear in the ROW area,
cross-tabbed with all the unique Val1 values in the COLUMN area, and the
corresponding counts of each Gender-Val1 combination in the DATA area. Yep,
the full show at one go.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik

 

[Pete_UK]

Try it this way:

=SUMPRODUCT((A1:A100="female")*(B1:B100=5))

I've assumed you have data in rows 1 to 100 - adjust the ranges to suit your
data, but you can't use full-column references unless you have XL2007.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

You can also use this array formula (Ctrl+Shift+Enter):

SUM(IF((A1:A5="Female")*(B1:B5=5),1))

Alternatively, if you are using Excel 2007, you can use the COUNTIFS()
function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[bosco_yip]

I am trying to countif - with two columns of information. if the values in
column A = certain criteria, i want to count the number of times a value
appears in Column B - not all values in column A correspond to column b
values. So if I want to see how many females received a "5", I need to see
how many "female" in column A have a corresponding value of "5" in column B.
(not all females have a "5", values can be 1 through 5)

Or try :

=SUMPRODUCT(--(A1:A10&B1:B10="female5"))

Regards
Bosco