Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count entries when two criteria are met
I need to count the number of entries in a spreadsheet where a chosen string
appears anywhere in column K and a (different) chosen string ALSO appears in column D. I have tried to call this by putting a formula in the cell where the result should appear. For example - count all the rows where "Smith" appears in column K and "Not present" appears in column D and put the result in cell A1. As far as I can discover, you can't use COUNTIF with two parameters and I believe SUMPRODUCT can be used, although I think I've read that you can't use SUMPRODUCT on entire columns. I've read all the posts I can find around this and - sorry all - I can't really understand what I should do. Would any kind person be able to tell me in VERY SIMPLE words if this can be done and how? Thanks in advance - I'm new to this and currently beaten!! Sue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count entries when two criteria are met
You're right...you can't use SUMPRODUCT when evaluating
entire columns. But the question is, do you really need to reference entire columns? Do you have 65,536 rows of data? Try using a formula like: =SUMPRODUCT((K1:K10000="string1")*(D1:D10000="stri ng2")) HTH Jason Atlanta, GA -----Original Message----- I need to count the number of entries in a spreadsheet where a chosen string appears anywhere in column K and a (different) chosen string ALSO appears in column D. I have tried to call this by putting a formula in the cell where the result should appear. For example - count all the rows where "Smith" appears in column K and "Not present" appears in column D and put the result in cell A1. As far as I can discover, you can't use COUNTIF with two parameters and I believe SUMPRODUCT can be used, although I think I've read that you can't use SUMPRODUCT on entire columns. I've read all the posts I can find around this and - sorry all - I can't really understand what I should do. Would any kind person be able to tell me in VERY SIMPLE words if this can be done and how? Thanks in advance - I'm new to this and currently beaten!! Sue . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count entries when two criteria are met
thank you Don, much appreciated
Sue "Don Guillett" wrote: use sumproduct on ranges such as a2:a200. Suggest using a defined name range that is self adjusting and use that name in the formula. -- Don Guillett SalesAid Software "SueJB" wrote in message ... I need to count the number of entries in a spreadsheet where a chosen string appears anywhere in column K and a (different) chosen string ALSO appears in column D. I have tried to call this by putting a formula in the cell where the result should appear. For example - count all the rows where "Smith" appears in column K and "Not present" appears in column D and put the result in cell A1. As far as I can discover, you can't use COUNTIF with two parameters and I believe SUMPRODUCT can be used, although I think I've read that you can't use SUMPRODUCT on entire columns. I've read all the posts I can find around this and - sorry all - I can't really understand what I should do. Would any kind person be able to tell me in VERY SIMPLE words if this can be done and how? Thanks in advance - I'm new to this and currently beaten!! Sue |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count entries when two criteria are met
Many thanks Jason, you're absolutely right and it works! So simple once
somebody explains it. Thanks again S "Jason Morin" wrote: You're right...you can't use SUMPRODUCT when evaluating entire columns. But the question is, do you really need to reference entire columns? Do you have 65,536 rows of data? Try using a formula like: =SUMPRODUCT((K1:K10000="string1")*(D1:D10000="stri ng2")) HTH Jason Atlanta, GA -----Original Message----- I need to count the number of entries in a spreadsheet where a chosen string appears anywhere in column K and a (different) chosen string ALSO appears in column D. I have tried to call this by putting a formula in the cell where the result should appear. For example - count all the rows where "Smith" appears in column K and "Not present" appears in column D and put the result in cell A1. As far as I can discover, you can't use COUNTIF with two parameters and I believe SUMPRODUCT can be used, although I think I've read that you can't use SUMPRODUCT on entire columns. I've read all the posts I can find around this and - sorry all - I can't really understand what I should do. Would any kind person be able to tell me in VERY SIMPLE words if this can be done and how? Thanks in advance - I'm new to this and currently beaten!! Sue . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the number of entries of specifc criteria is met | Excel Worksheet Functions | |||
count entries based on criteria in a different column | Excel Worksheet Functions | |||
count entries based on criteria in a different column | Excel Worksheet Functions | |||
Count of entries meeting criteria | Excel Worksheet Functions | |||
I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions |