Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I Need to Count Number of Entries Based on Two Criteria
I need to count the number of entries in column B only if column J does not
equal "1" AND column B is 3. Not looking for a "sum" or to "multiply", I simply need a count of the number of entries in column B that match these two criteria. I have tried combining functions through IF and COUNTIF, but have not been successful. Any ideas? Thanks :) |
#2
|
|||
|
|||
In a helper column put this and copy down............
=IF(AND(B13,J1<1),1,"") Then, sum the helper column, or count the 1's whichever you prefer.......... Vaya con Dios, Chuck, CABGx3 "Jones" wrote in message ... I need to count the number of entries in column B only if column J does not equal "1" AND column B is 3. Not looking for a "sum" or to "multiply", I simply need a count of the number of entries in column B that match these two criteria. I have tried combining functions through IF and COUNTIF, but have not been successful. Any ideas? Thanks :) |
#3
|
|||
|
|||
Try this Jones
=SUMPRODUCT((J1:J10<1)*(B1:B103)) -- Regards Ron de Bruin http://www.rondebruin.nl "Jones" wrote in message ... I need to count the number of entries in column B only if column J does not equal "1" AND column B is 3. Not looking for a "sum" or to "multiply", I simply need a count of the number of entries in column B that match these two criteria. I have tried combining functions through IF and COUNTIF, but have not been successful. Any ideas? Thanks :) |
#4
|
|||
|
|||
try using an Array function (Excel Help is good for this - you could also load the 'Conditional Sum Wizard' Add-In from the Tools menu to help you write the formula ) something like =COUNT(if((B1:B103)*(J1:J10<1),B1:B10)) Where * represents AND & + represents OR Can also replace COUNT with SUM if required. (note : you need to press and hold Ctrl+Shift when pressing Enter after typing the formula - this tells Excel that this is an Array Function. (make sure your ranges match; aviod referencing whole columns as doing this multiple times will place strain on Excels calculation cycle (noticeable on my laptop when using around 50 of these in a workbook)) hope this helps Jones -- optionbase1 ------------------------------------------------------------------------ optionbase1's Profile: http://www.excelforum.com/member.php...o&userid=25212 View this thread: http://www.excelforum.com/showthread...hreadid=386977 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Creating a certain number of entries based on a number in a cell | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Count based on another column | Excel Discussion (Misc queries) | |||
Count rows based on multiple criteria | Excel Worksheet Functions |