COUNTIF Multiple Conditions

How do I use countif with multiple conditions

Column B has dates
Column C has Various data or Blank

I need to count the number of non blanks in column C if column A is one
of 3 dates

I have tried the array formula below, but am getting the wrong answer

{=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100=A7)*(Sheet1!$B$2:$B$100=A8)*(Sheet1!$C$2:$C$100<>""))}

A6, A7 and A8 are the cells witth the dates in


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=468131

0
9/16/2005 6:11:07 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
505 Views

Similar Articles

[PageSpeed] 15

=SUM(((Sheet1!$B$2:$B$100=A6)+(Sheet1!$B$2:$B$100=A7)+(Sheet1!$B$2:$B$100=A8))*(Sheet1!$C$2:$C$100<>""))

array entered with Ctrl+Shift+Enter

or entered normally

=SUMPRODUCT((($B$2:$B$100=A6)+($B$2:$B$100=A7)+($B$2:$B$100=A8))*($C$2:$C$100<>""))

Hope this helps
Rowan

Paul Sheppard wrote:
> How do I use countif with multiple conditions
> 
> Column B has dates
> Column C has Various data or Blank
> 
> I need to count the number of non blanks in column C if column A is one
> of 3 dates
> 
> I have tried the array formula below, but am getting the wrong answer
> 
> {=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100=A7)*(Sheet1!$B$2:$B$100=A8)*(Sheet1!$C$2:$C$100<>""))}
> 
> A6, A7 and A8 are the cells witth the dates in
> 
> 
0
9/16/2005 7:20:17 AM
or slightly shorter might work

=SUMPRODUCT(($B$2:$B$100=A6)*($B$2:$B$100={A7,a8})*($C$2:$C$100<>""))

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Rowan" <rowanzsaNotThis@hotmail.com> wrote in message
news:OoskZ8ouFHA.3528@TK2MSFTNGP15.phx.gbl...
>
=SUM(((Sheet1!$B$2:$B$100=A6)+(Sheet1!$B$2:$B$100=A7)+(Sheet1!$B$2:$B$100=A8
))*(Sheet1!$C$2:$C$100<>""))
>
> array entered with Ctrl+Shift+Enter
>
> or entered normally
>
>
=SUMPRODUCT((($B$2:$B$100=A6)+($B$2:$B$100=A7)+($B$2:$B$100=A8))*($C$2:$C$10
0<>""))
>
> Hope this helps
> Rowan
>
> Paul Sheppard wrote:
> > How do I use countif with multiple conditions
> >
> > Column B has dates
> > Column C has Various data or Blank
> >
> > I need to count the number of non blanks in column C if column A is one
> > of 3 dates
> >
> > I have tried the array formula below, but am getting the wrong answer
> >
> >
{=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100=A7)*(Sheet1!$B$2:$B$100=A8
)*(Sheet1!$C$2:$C$100<>""))}
> >
> > A6, A7 and A8 are the cells witth the dates in
> >
> >


0
Don
9/16/2005 12:30:30 PM
Maybe...

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!$B$2:$B$100,A6:A8,0))),--(Sheet1!$C$
2:$C$100<>""))

Hope this helps!

In article 
<Paul.Sheppard.1vfwqb_1126854306.7211@excelforum-nospam.com>,
 Paul Sheppard 
 <Paul.Sheppard.1vfwqb_1126854306.7211@excelforum-nospam.com> wrote:

> How do I use countif with multiple conditions
> 
> Column B has dates
> Column C has Various data or Blank
> 
> I need to count the number of non blanks in column C if column A is one
> of 3 dates
> 
> I have tried the array formula below, but am getting the wrong answer
> 
> {=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100=A7)*(Sheet1!$B$2:$B$100=A8)*
> (Sheet1!$C$2:$C$100<>""))}
> 
> A6, A7 and A8 are the cells witth the dates in
0
domenic22 (716)
9/16/2005 12:49:34 PM
Hello

I want to count everything in Column K that is either greater than or 
to 75% AND less than or equal to 125%. Is there a way to do this?

This is the formula i used and it works but it seems a bit lon
winded.

thanks

=COUNT(K3:K29)-COUNTIF(K3:K29,"<75%")-COUNTIF(K3:K29,">125%"

--
cchen21
-----------------------------------------------------------------------
cchen212's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2995
View this thread: http://www.excelforum.com/showthread.php?threadid=46813

0
12/28/2005 6:54:18 PM
=COUNTIF(K3:K29,">=0.75")-COUNTIF(K3:K29,">1.25")

or

=SUMPRODUCT(--(K3:K29>=0.75),--(K3:K29<=1.25))

will both retrun the same count


-- 

Regards,

Peo Sjoblom


"cchen212" <cchen212.20rk9m_1135796101.2338@excelforum-nospam.com> wrote in
message news:cchen212.20rk9m_1135796101.2338@excelforum-nospam.com...
>
> Hello
>
> I want to count everything in Column K that is either greater than or =
> to 75% AND less than or equal to 125%. Is there a way to do this?
>
> This is the formula i used and it works but it seems a bit long
> winded.
>
> thanks
>
> =COUNT(K3:K29)-COUNTIF(K3:K29,"<75%")-COUNTIF(K3:K29,">125%")
>
>
> -- 
> cchen212
> ------------------------------------------------------------------------
> cchen212's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=29951
> View this thread: http://www.excelforum.com/showthread.php?threadid=468131
>


0
terre081 (3244)
12/28/2005 7:03:56 PM
Reply:

Similar Artilces:

VLLOKUP across multiple workbooks
One of our Financial analyst is using VLOOKUP formula using 2-3 different workbooks. Formula on workbook 1 is =IF(AsofDate_Match_YN,VLOOKUP(I$471,'\\server1\Performance Data\ [Summary Reports.xls]Summary1'!$A$1:$AZ$10,4,FALSE),NA())*100 Problem is actually with file path. There's a mapping to \\server1 as F drive and whenever we change the above formula to reflect F: \Performance Data, after opening the file again it reverts back to \ \server1\ How can we put persistant F:\ Thanks! Hi, I'd suggest that if you have a lot of these formulas, that what you can do is select al...

My home network and multiple profiles with Outlook
I just bought two Dells with XP and Office (Small Business) 2003 and plan to wireless network my wife's and my computer with mine being the base station or hub. (by the way, I'm a computer illiterate, so bear with me) I have created individual profiles for my wife and I on my computer. My Outlook question for anyone is this: When I get my wife's computer up and running with a wireless network and she downloads Outlook e-mail while logged in on her computer under her profile, where is her Outlook personnal data (messages, calander, address book) stored? If it's st...

condition format for surrounding cells
Need help, I have column A the have 3 conditional format that is color coded depending value, I would like to have columns B, C, D,E (which are text) that could changes to the same color as column A when that changes. If anyone could help that would be greatly appriciated. Hi try something like the following: - select A1:E1 - in the conditional format dialog enter a formula such as =$A1="condition1" -- Regards Frank Kabel Frankfurt, Germany "little rusty with excel" <little rusty with excel@discussions.microsoft.com> schrieb im Newsbeitrag news:E9563E6B-7BCF-4B2...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Sumif with multiple sum ranges in a sheet
Hi experts, I need to sum multiple columns in a range which meets a criteria in one of the columns of the range. For example C1 C2 C3 R1 A 10 10 R2 B 4 5 R3 A 2 1 I need to sum columns C2&C3 if value in column C1 is equal to A. In this case the result should be 23. thanks Not thoroughly tested =SUMPRODUCT(--(A1:A3="A"),B1:B3+C1:C3) "MA" wrote: > Hi experts, > > I need to sum multiple columns in a range which meets a criteria in one of > the...

Need help with CountIF function
Hello, Please help. I have a columne A with department codes A, B, C, ... the department codes are multiple lines, column B with numbers when there are activity in that department. Can I do a countif function or other function to summerize how many activity each department? To count how many times there is activity for dept A: =SUMPRODUCT(--(A2:A100="A"),--(ISNUMBER(B2:B100)) To sum the numbers in column B associated with dept A: =SUMIF(A:A,"A",B:B) -- Best Regards, Luke M "Flo" <Flo@discussions.microsoft.com> wrote in message...

REPLACE across multiple excel files
I've built a series of data sheets, across several different folders o our network drive. I now need to replace one of the names in the data with another one. e.g. "Jimmy" > "James" Is there any cunning macro that would recursively step through eac file in each folder, replacing the text: before I resort to doing i manually. :( Cheers, Jo -- Message posted from http://www.ExcelForum.com Hi, I don't know how cunning this macro is, but it looks for "john" and replaces with "BIGJOHN". Try on TEST files 1st - you have to itemize each file...

Problems using xLOOKUP/MATCH in formulas for conditional formatting
Hi, I have some problems using conditional formatting in Excel 2007. I have a matrix of values divided by a diagonal, like so: A B C ... .. . A - 0 0 .. . B 3 - 1 . C 4 2 - .... .. . ... . .. (Sorry for the bad ASCII-drawing, but you get the idea) Now, I want to format the values in the lower part of the diagonal based on an evaluation of the values on the upper part of the diagonal. I am trying to use the following formula in my conditional formatting: =HLOOKUP(INDIRECT(ADDRESS(ROW();1));$A$2:$J $9;MATCH(INDIRECT(ADDRESS(1;COLUMN()));$A$2:$A$9;0);FALSE)<alp...

Conditioning Formatting Based On Due And Overdue Dates
I'm trying to set up a sheet where if a date is inputted in Cell A, it would generate a due date in Cell B, and a date for actual completion would have to be entered in Cell C. I'm trying to set it up so that if the Cell C is blank, and it is past the due date, then the date in Cell B would be in red. Also, how do you set Cell B up to stay blank (instead of having a date with the year 1900 in it) if nothing has yet been entered in Cell A? -- Israel Rodriguez http://home.earthlink.net/~isrodriguez7/ Israel first, how to "set Cell B up to stay blank": =IF(A2="&q...

multiple modal dialogs
Hi, Anybody an idea how to have multiple modal dialogs in an application? I have an application that should allow multiple frame windows in the same time. The problem is, that these frame windows can have there own modal dialogs / message boxes. If a modal dialog (or message box) is open in one frame, and I switch to an other frame, and open there an other modal dialog (or a message box comes as a response to some event), the application enters in the modal loop if the new modal dialog. The problem is, that if the first modal dialog is closed with it's frame window, the frame window...

split/multiple categories 2006
I can't seem to split transactions anymore. I enter all the data hit done then if i go to another transaction and then go back the splits are gone. Essential Register? Downloaded transaction data? Yodlee? You haven't given us much to go on. I routinely do this in M06 Trial--in Advanced register with all transaction data entered by hand. "chip" <chip@discussions.microsoft.com> wrote in message news:BA9A883B-3C37-4B31-B71E-C258E8B751CF@microsoft.com... > I can't seem to split transactions anymore. I enter all the data hit > done then if i go to an...

How to refresh multiple users buffers
We have a networked Access 2003 application (.adp) with a SQL Server 2000 backend. Changes made by one user to a data row are not visible to another user viewing the same row unless both users quit the application. I presume this is some type of buffer situation, where both users get complete buffers (copies) of the data(base) when they launch the application. And then when the first user moves off the updated row, the second user's buffer is not automatically updated. Is my analysis close to correct? Is there any way to force or accomplish a refresh of one or many user's...

moving multiple controls on resize easily
I have a graphing app that has a graph on the upper 3/4 of the dialog, and the lower 1/4 are basic controls (buttons, cedit boxes, static labels, etc). The user needs to have he ability to resize the dialog so that the chart can expand and shrink. While the controls on the bottom dont need to change size, they do need to move.. Is there an easier way to move all objects without having to recalculate their postions and use MoveWindow on each control? Thanks, Nick >I have a graphing app that has a graph on the upper 3/4 of the dialog, and >the lower 1/4 are basic controls (b...

Multiple e-mail addresses for Contacts
Our office is now running XP. In Office 97 if you had one contact with nultiple e-mail addresses they would show up in the Contacts addresses list as - username (1), username (2). In XP this is not happening. The contact is showing up twice - but with no distinction and the same e-mail address. I have tried to save with each different address and it keeps defaulting back to the first one. Any help would be appreciated. If you scroll over to the right in the address book view, do you see the addresses? Teresa Mace wrote: > Our office is now running XP. > > In Office 97 if y...

Multiple Sub Domains with own Catch-All Mailbox?
I have a client who wants to create multiple email sub-domains (no problems settiung that up) but then has requested that each sub-domain has it's own catch-all mailbox for unresolved emails. Anyone have any pointers? Thanks Andy On Tue, 11 Jan 2005 05:21:04 -0800, Andy H <AndyH@discussions.microsoft.com> wrote: >I have a client who wants to create multiple email sub-domains (no problems >settiung that up) but then has requested that each sub-domain has it's own >catch-all mailbox for unresolved emails. > >Anyone have any pointers? > >Thanks > >...

sending to multiple recpients without names listed.
How can I send the same email to multiple recipients without their name or address appearing in the "to" field? I want to send a message to all our clients but I don't want them to have the contact info for the other clients. Thanks, Hayley Put the addresses in the BCC field and use your own e-mail address in the To field. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:2cd8001c39413$4de86070$a601280a@phx.gbl, Hayley wrote: > H...

graphing multiple data points from refreshed cells
I have created a spreadsheet that imports current values of certain data points from an external server (PI). These current values run through a series of calculations to determine a single output in a cell. By hitting the F9 button, I can refresh the current values and subsequently refresh the output data point (cell). I'd like to capture the value of the output cell each time I refresh it and graph the output data point versus time. Does anyone know how to do this? How about putting a button from the forms toolbar on the worksheet. I created a new worksheet (sheet2) and p...

question about countif
Hi I want to count the number of "a" in column "B" which is easy (countif(b4:b50,"a") but I also want to see what the count is when column "A" is taken in to the equation. In column A is the date. So I want a count the Number of "a" in column B where the date in column A is in the last 12 months/365 days? Thanks Jon This may work for you: =sumproduct(--(a4:a50>=date(year(today())-1,month(today()),day(today()))), --(b4:b50="A")) Adjust the ranges to match--but you can't use whole columns (except in xl200...

search condition- copy
Hi, I have a column DR. Some of it cells are empty and the others are not, they contain alphabets such as T, TA, SSB... (19 different sets of letters). The range I need is DR8:DR200 How can I search the cells of this column in a way that if the cell is full (contains any of the mentioned alphabets) to select the row of that cell and copy the entire row to another work sheet in a different workbook with the name of "WV" and put it in row 7, the next row to be cop will follow in row 8 in the new worksheet. Is this possible? Any help is appreciated Khalil Handal You may w...

How to update data from multiple sheets to one specific sheets
In worksheet in sheet-1 I have about 15,000 rows in the following columns A1(Unique id) A2 Names of persons A3 Address A4 Amount for the year 2005 A5 Amount for the year 2006 A6 Amount for the year 2007 (Blank / no date) In sheet-2 I have 22,500 rows and have following columns A1 (Unique id) A2 Names of person A3 Address A4 Amount for the year 2007 Same above column in sheet-3, sheet-4 and sheet-5 I have to update the rows of column A6 of sheet 1 through column A4 of sheet-2, sheet-3, sheet-4 and sheet-5 Please help to filter the data Khawaja Saab you can do the foll...

windows xp, can not select multiple users in lusrmgr.msc
Hello from Spain, i was studying lusrmgr.msc, the console for the management of local users and groups, for my mcdst certification when i realized i couldn't perform the action of selecting some users with the shift key neither with the control key. In the book they say it's possible... what am i doing wrong? I'm working on a windows XP Pro 2002 service pack 3. Thanks On Wed, 18 Aug 2010 20:35:58 +0200, "m0rg4n" <edu.bit.es@gmail.com> wrote: >Hello from Spain, > >i was studying lusrmgr.msc, the console for the management of loc...

How to configure OL2003 with exchange svr 2003 host multiple domains?
Dear, I have setup thee internal domains to exchange data using my single exchange server 2003,I added three MX records for each server of them in my local DNS, When I want to configure my Outlook2003 with Exchange Server Account of them, it gives me an error tell me that "Name Could not be resolved. The bookmark is not valid". although I can configure these account as POP3 or IMAP4., but ot as Exchange Server Account. I already added e-mail address for every user with his corresponding domain, and configured the Default Reciepient Policy to have this server is the responsible...

OL2003: Multiple folder view
We're using Outlook 2003 in Exchange environment and we're using common "office calendar" in exchange public folders for all kinds of little happenings. When in calendar view, I can check the "office calender" and view beside my own calender. However, when I restart Outlook and return to calendars, I have to check the "office calender" again. - Is there any way to make Outlook remember this setting? Thanks, Jussi No, it's not persistent. From Outlook's Help task pane (F1 to open it) choose Communities and enter a suggestion. So that those o...

Multiple Qualifiers
I am trying to do a Count to get the number of locations that have a total of >= 22 This is the incorrect formula: =SUMPRODUCT(--(Data!$F$1:$F$19272=51160),--(Data!$S$1:$S$19272>=22)), my desired answer would be 1, or 50% Column F Column D 51160 34 51160 20 230 10 230 62 20 28 The second part of your column, according to your example should refer to column D, not S. But if the smaller numbers really are in S, then you need to examine both columns and make sure the numbers are really numbers, and not text. Your ...

Conditional formatting in a bar chart
I have a horizontal bar chart for weekly sales figures i.e. this years sales minus last years sales . I am using Excel 2007. I have positive and negative sales figures. Everything works fine but I would like to chart negative sales as red and positive sales as green on the same chart? I am new to Excel and I am not comfortable using VBA code, if that helps. Thanks Hi, No need for vba. Just use 2 series and some formula. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Charles Eaves wrote: > I have a horizontal bar chart for weekly sales figures i.e. this ye...