Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jones
 
Posts: n/a
Default 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   Report Post  
CLR
 
Posts: n/a
Default

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   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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   Report Post  
optionbase1
 
Posts: n/a
Default


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
Creating a certain number of entries based on a number in a cell PPV Excel Worksheet Functions 4 June 16th 05 10:25 PM
Count Number of Characters in a cell? AHJuncti Excel Discussion (Misc queries) 2 June 16th 05 07:39 PM
Count based on another column Chance224 Excel Discussion (Misc queries) 1 March 4th 05 05:33 PM
Count rows based on multiple criteria Murph Excel Worksheet Functions 1 October 28th 04 07:13 AM


All times are GMT +1. The time now is 09:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"