Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT A CORRESPONDING CELL IF A CONDITION IS MET
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT A CORRESPONDING CELL IF A CONDITION IS MET
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 --- "Sandizet" wrote: 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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT A CORRESPONDING CELL IF A CONDITION IS MET
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 "Sandizet" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT A CORRESPONDING CELL IF A CONDITION IS MET
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 "Sandizet" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT A CORRESPONDING CELL IF A CONDITION IS MET
"Sandizet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count after given condition is met | Excel Discussion (Misc queries) | |||
Count Numeric Value with condition | Excel Discussion (Misc queries) | |||
Count with condition range | Excel Worksheet Functions | |||
Count cells with condition | Excel Worksheet Functions | |||
count col-A if col-B = condition | Excel Worksheet Functions |