Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count cells using multiple criteria
Can anyone show me how to count the number of rows containing two or more
criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? |
#2
|
|||
|
|||
Hi
I reckon SUMPRODUCT is what you need. Something like =SUMPRODUCT(--(A2:A1000="DO")*--(B2:B1000="RD11")) -- Andy. "Alex68" wrote in message ... Can anyone show me how to count the number of rows containing two or more criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? |
#3
|
|||
|
|||
One way:
=SUMPRODUCT(--(A1:A1000="DD"),--(B1:B1000="RD11")) In article , Alex68 wrote: Can anyone show me how to count the number of rows containing two or more criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? |
#4
|
|||
|
|||
one method would be to use sum product
=sumproduct(--(A1-A1000="DO"),--(B1-B1000="RD11")) the "=" in parenthesis makes the results of the parenthesis be true or false the "--(" changes the true or false to a 1 or 0 you must have equal ranges for each segment in sumproduct and can not use entire columns or rows listed as "A:A" etc. "Alex68" wrote: Can anyone show me how to count the number of rows containing two or more criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? |
#5
|
|||
|
|||
There's no need to use -- if you're going to multiply the arrays before
you pass the result to SUMPRODUCT. See http://www.mcgimpsey.com/excel/doubleneg.html In article , <Andy B wrote: I reckon SUMPRODUCT is what you need. Something like =SUMPRODUCT(--(A2:A1000="DO")*--(B2:B1000="RD11")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Count of items using multiple criteria | Excel Worksheet Functions | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |