| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Criteria for CountIF
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
I do this with SUMPRODUCT
=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
On Aug 1, 12:41*pm, Rob wrote:
Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. *Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. First off, you haven't actually defined your criteria in that formula, you have only identified the range. COUNTIF(Range, Criteria), e.g. COUNTIF(AA2:AA301,1). SUMPRODUCT is your solution. =SUMPRODUCT(--(AA$2:A$301=1)*(D$1:D$301="yes")) That should do it for you. Steven |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
Just to add to Barb's response...
If those values in AA2:AA301 are really numbers (not text): =SUMPRODUCT(--(AA2:AA301=1),--(D2:D301="YES")) Barb Reinhardt wrote: I do this with SUMPRODUCT =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
On Aug 1, 12:46*pm, Barb Reinhardt
wrote: I do this with SUMPRODUCT =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. *Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. Barb, you beat me to it. I talk too much. Steven |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
Thanks for the multiple and quick responses but when I try any of them I
either get a 0 or #N/A as a result but it should come back with 11 for the answer. I did a direct Copy/Paste of both formula given. Thanks again for helping, I hope to get this if possible. " wrote: On Aug 1, 12:46 pm, Barb Reinhardt wrote: I do this with SUMPRODUCT =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. Barb, you beat me to it. I talk too much. Steven |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
Nevemind, I found the problem. Here's what it was.
Provided Formula: =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) Gives 0 Corrected: =SUMPRODUCT(--(AA2:AA301=1),--(D2:D301="YES")) Gives 11 Again, I Thank You VERY much! "Rob" wrote: Thanks for the multiple and quick responses but when I try any of them I either get a 0 or #N/A as a result but it should come back with 11 for the answer. I did a direct Copy/Paste of both formula given. Thanks again for helping, I hope to get this if possible. " wrote: On Aug 1, 12:46 pm, Barb Reinhardt wrote: I do this with SUMPRODUCT =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. Barb, you beat me to it. I talk too much. Steven |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple Criteria for CountIF
I'm guessing you have "N/A" in cells in one of the columns. If so, that
makes it even more fun! :) Try this =SUMPRODUCT(--(IF(ISNUMBER(AA2:AA301),AA2:AA301=1)),--(IF(ISTEXT(D2:D301),D2:D301="YES"))) Commit with CTRL SHIFT ENTER -- HTH, Barb Reinhardt "Rob" wrote: Thanks for the multiple and quick responses but when I try any of them I either get a 0 or #N/A as a result but it should come back with 11 for the answer. I did a direct Copy/Paste of both formula given. Thanks again for helping, I hope to get this if possible. " wrote: On Aug 1, 12:46 pm, Barb Reinhardt wrote: I do this with SUMPRODUCT =SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) -- HTH, Barb Reinhardt "Rob" wrote: Hello, I was wanting to have a formula that counts all the cells that meet two sets of criteria but the only way I know of doing this, shown below, does not work. Can someone show me how I can do this properly? =COUNTIF(AND(AA2:AA301="1",D2:D301="YES")) Thank very much. Barb, you beat me to it. I talk too much. Steven |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
| COUNTIF with multiple criteria | Excel Programming | |||
| COUNTIF - Multiple Criteria | Excel Worksheet Functions | |||
| COUNTIF Multiple Criteria | Excel Programming | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
