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 :)

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 :)

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 :)

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