# I Need to Count Number of Entries Based on Two Criteria

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. ## Re: I Need to Count Number of Entries Based on Two Criteria

In a helper column put this and copy down............

=IF(AND(B1>3,J1<>1),1,"")

Then, sum the helper column, or count the 1's whichever you
prefer..........

Vaya con Dios,
Chuck, CABGx3

"Jones" <Jones@discussions.microsoft.com> wrote in message
news:67BD77D1-1ACB-49A1-845C-A7623FD28325@microsoft.com...
> 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. ## Re: I Need to Count Number of Entries Based on Two Criteria

Try this Jones

=SUMPRODUCT((J1:J10<>1)*(B1:B10>3))

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Jones" <Jones@discussions.microsoft.com> wrote in message news:67BD77D1-1ACB-49A1-845C-A7623FD28325@microsoft.com...
>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. ## Conditional sum

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:B10>3)*(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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1