How do I count only if either/ or exist

I am attempting to count how many times there is a number greater than zero 
in either column A or column B.  

My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 and 
B=1, count only once.

Thank you for any help
0
Utf
1/24/2010 10:32:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
844 Views

Similar Articles

[PageSpeed] 49

"wildbillyd" wrote:
> My goal is if A=0 and B=0, do not count.
> If A=0 and B=1, count.  If A=1 and B=1,
> count only once.

One way:

=sumproduct(--((A1:A10>0)+(B1:B10>0)>0))

The "+" functions as "OR" in this context.  The OR function cannot be used 
in this context; it will not be interpreted as intended.  The "--" (double 
negation) is used to convert the truth values (TRUE and FALSE) to numeric 
values (1 and 0), which SUMPRODUCT looks for.  Any arithmetic operation with 
truth values will accomplish the same thing.


----- original message -----

"wildbillyd" wrote:
> I am attempting to count how many times there is a number greater than zero 
> in either column A or column B.  
> 
> My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 and 
> B=1, count only once.
> 
> Thank you for any help
0
Utf
1/24/2010 10:50:01 PM
What about something like this...

=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")

-- 
Rick (MVP - Excel)


"wildbillyd" <wildbillyd@discussions.microsoft.com> wrote in message 
news:F2ECAED3-6676-4099-BF65-542E68FCED19@microsoft.com...
>I am attempting to count how many times there is a number greater than zero
> in either column A or column B.
>
> My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 
> and
> B=1, count only once.
>
> Thank you for any help 

0
Rick
1/25/2010 12:38:46 AM
"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:ei48$aVnKHA.5312@TK2MSFTNGP04.phx.gbl...
> What about something like this... 
> =COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")

Rick, does that meet that OP's condition that "[if] A=1 and B=1, count only 
once"?


----- original message -----

"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:ei48$aVnKHA.5312@TK2MSFTNGP04.phx.gbl...
> What about something like this...
>
> =COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")
>
> -- 
> Rick (MVP - Excel)
>
>
> "wildbillyd" <wildbillyd@discussions.microsoft.com> wrote in message 
> news:F2ECAED3-6676-4099-BF65-542E68FCED19@microsoft.com...
>>I am attempting to count how many times there is a number greater than 
>>zero
>> in either column A or column B.
>>
>> My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 
>> and
>> B=1, count only once.
>>
>> Thank you for any help
> 

0
Joe
1/25/2010 1:17:39 AM
Here's another one...

=SUMPRODUCT(SIGN((A1:A100>0)+(B1:B100>0)))

This version is slightly more efficient on larger ranges.

-- 
Biff
Microsoft Excel MVP


"wildbillyd" <wildbillyd@discussions.microsoft.com> wrote in message 
news:F2ECAED3-6676-4099-BF65-542E68FCED19@microsoft.com...
>I am attempting to count how many times there is a number greater than zero
> in either column A or column B.
>
> My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 
> and
> B=1, count only once.
>
> Thank you for any help 


0
T
1/25/2010 2:15:46 AM
No, it doesn't... I skimmed the OP's question too fast and completely missed 
that requirement. Thanks for pointing it out.

-- 
Rick (MVP - Excel)


"Joe User" <joeu2004> wrote in message 
news:%231NyywVnKHA.5524@TK2MSFTNGP05.phx.gbl...
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:ei48$aVnKHA.5312@TK2MSFTNGP04.phx.gbl...
>> What about something like this... 
>> =COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")
>
> Rick, does that meet that OP's condition that "[if] A=1 and B=1, count 
> only once"?
>
>
> ----- original message -----
>
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:ei48$aVnKHA.5312@TK2MSFTNGP04.phx.gbl...
>> What about something like this...
>>
>> =COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "wildbillyd" <wildbillyd@discussions.microsoft.com> wrote in message 
>> news:F2ECAED3-6676-4099-BF65-542E68FCED19@microsoft.com...
>>>I am attempting to count how many times there is a number greater than 
>>>zero
>>> in either column A or column B.
>>>
>>> My goal is if A=0 and B=0, do not count.  If A=0 and B=1, count.  If A=1 
>>> and
>>> B=1, count only once.
>>>
>>> Thank you for any help
>>
> 

0
Rick
1/25/2010 3:57:11 AM
Reply:

Similar Artilces:

Counting populated rows in excel
I have some rows populated in an excel sheet. Is there a way I can writ a macro to count the number of populated rows ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you pick out a column that's always filled in? If yes, then maybe you could do this: Option Explicit Sub testme() Dim LastRow As Long With Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With MsgBox LastRow & " is th...

Counting Instances Of Item In A Column
I have a table with data along the lines of the below. A_______B______________C_______D 1_______Success/Fail_____Type_____Time 2_______S______________X_______34 3_______F______________Z _______45 4_______S______________Y _______43 5_______F______________X_______34 6_______S______________Z_______34 I want to work out the success rate for each type, which I thought would be easy but has proven surprisingly difficult for an Excel novice. Obviously to work out the success rate for type Z I would need to count the number of Zs in column A and the number of successes S corresponding to each Z an...

counting a range that has only general format
I'm attempting to count cells in a range that have a general format. Need help with countif. -- 1 putt Hi, AFAIK that can't be done with countif. How about a UDF. Alt +F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code beolw in call with =CountGeneral(A!:A10) Function CountGeneral(rng As Range) As Long For Each c In rng If c.NumberFormat = "General" Then CountGeneral = CountGeneral + 1 End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothes...

Adding an existing IRA account
Hello, I want to add my Dain Rausher IRA to money as a retirement option also my wife has a retirement plan. How do I go about doing this? You don't Money mention version/edition. That might be vitally important here, now that Microsoft has balkanized the product. The general answer for all but M07 Money Essentials (MEss) is to create an "Investment Account" for each IRA and/or 401(k). You can define these specific types of Investment Account and Money will treat it more or less correctly from a tax estimating and reporting perspective right up until the time you go to ...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Attn: Allen Browne - Old Question
Allen - I haven't tried doing the response that you gave me regarding the question below. I'm having a major senior moment and can't figure out what the DLookup syntax would be. Any help? BTW the table name is tblContractorProjects... Thanks Jeff ----------------------------------------- Use the BeforeUpdate event procedure of the form to perform the validation. Use DLookup() to see if an overlapping entry exists in the table. Assuming contractStartDate and contractEndDate are required fields (so you don't have to handle overlapping dates when one of the fields is b...

Character Count Range of Cells
How can I get a character count for text in a large range of cells in a worksheet? I can only get LEN to count a single cell without a #VALUE! error. -- michimac You could use {=SUM(LEN(A1:A20))} Note that the curly brackets indicate an Array formula DO NOT type them simply type =SUM(LEN(A1:A20)) and the prest Ctrl+Shift+Return the formula will be entered and the brackets as well. Change the range to suit -- _______________________ Naz, London "michimac" wrote: > How can I get a character count for text in a large range of cells in a > worksheet? I can on...

Count function problem
I have a formula in a cell which reads: "=IF(COUNT(D13:G13)>=2,J13+MAX(H13,$B$43),0)" When I created this workbook template, the cells from D13 to G13 were blank until I filled them in, and this formula worked fine. However, I've made a change to the workbook. Now I've made D13 through G13 equal to the values in a range of cells from other sheets in the workbook (for e.g. D13 is "=Sheet1!D13". The above formula is now generating the number from B43 by default, even though the cells from D13 to G13 are blank. The "COUNT" help in Excel says: "...

count &sum
I need the result of a formula to show the sumber in a range of cells only if it appears 3 or more times. so if A1 = 3, B1=4, C1=3, D1=3, E1=1 and F1=4 I need G1 to show 3 Any ideas? Thanks Hi and what do you want the formula to return if more than one number occurs 3 opr more times? "Steven Hook" wrote: > I need the result of a formula to show the sumber in a range of cells only > if it appears 3 or more times. > so if A1 = 3, B1=4, C1=3, D1=3, E1=1 and F1=4 I need G1 to show 3 > Any ideas? > Thanks > > > It can return an error, it's for checkin...

Fixing A Dialog Box in an Existing Program Using Visual Studio
I have a commercial contact manager which was written for Win95. The program is no longer made (another company brought it out, and then discontinued it -- nice going), but I still use it. One of the major dialog screens, the one used to fill in all the contact data (name, address, etc.) has a list box from which to select the Group(s) to which the contact should be assigned. The list box is only three rows, even though the form has room for five rows. This is a minor but irritating design glitch, especially when I have 2 or 3 dozen groups to scroll through; it surely would have been fixe...

Open existing excel file and can't edit it.
If I open a pre-existing Excel file it is stuck highlighting the cell. If I move the cursor it's like using the highlight-drag function and a group of files are highlighted. I can't enter the menu and hitting "esc" doesn't clear the problem. I have to bring up the Windoes Task Manager to kill the Excel session to end Excel. If I open a NEW "workbook", I can edit the cells, but if I go to the file menu, the "open" selection is NOT available. I've deleted and re-installed Excel and Microsoft Office to no avail. I'm using Office 2002. Thanks ...

Count rows #2
Is there any formula to have excel count the number of rows in a rang where all the cells in that row meet some criteria? Countif will no work because there are multiple criteria -- keith697 ----------------------------------------------------------------------- keith6973's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1479 View this thread: http://www.excelforum.com/showthread.php?threadid=26421 look at the SUMPRODUCT function e.g. =SUMPRODUCT(--(A1:A100="Cat"),--(B1:B100="Dog"),--(C1:C100>100)) will count all the items with Cat in co...

Adding to existing file
is it possible to have a Publisher file and insert another file within the first one. Ex: file 1 is open and has two pages in it. I then want to insert file #2 (Which has 3 pages in it) and make my document have a total of 5pages now???? Hi david (friedman@peace-inc.org), in the Microsoft� newsgroups you posted: || is it possible to have a Publisher file and insert another || file within the first one. || || Ex: file 1 is open and has two pages in it. || || I then want to insert file #2 (Which has 3 pages in it) || and make my document have a total of 5pages now???? No David, you cannot...

Counting unique items in a range
Hi all I found some useful examples in Chip Pearson's site for counting unique items in a range. These are all based on variations of an array formula of the form {=sum(expression)} I tried without success adapting these to the form {=subtotal(9,expression)} in order to count the unique visible items in an autofiltered range. Help, please? -- Return email address is not as DEEP as it appears Hi think I 'captured' this formula from Peo Sjoblom: Use the following array formula (entered with CTRL+SHIFT+ENTER): =SUM(--IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW (A2:A100)),,1...

Count occurences over range if a certain criteria met
Hi I have a named range, called Servers (B2:I50). Within this range I want to count how many entries begin with New, so figure I need to use Left somehow, but am not sure how I can use this in a single cell with some countif/sumproduct function to count them all. Can anyone help please? B C New1 Server C Server A new3 Server B Server E Server C Server F New2 Server G Server B Server D Server A New 4 So this would return 4. Cheers Rich =COUNTI...

combine existing docs into one
how do you combine existing docs into one doc? Say I have made a monthly calendar, Sept - July, each one as a seperate saved doc. Now I want to put them in sequential order in one doc. Thanks Copy/paste. You can have many instances of Publisher open at one time. Insert however many pages you need in your new publication. Try Edit, Office Clipboard, you can collect 24 items. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "joseph" <joseph@discussions.m...

Count displayed rows when using filters
Is there a set of worksheet functions analogous to the COUNTx functions, but that count only data dislayed when using AutoFilter Solutions involving SUBTOTALS, or specially coded value in various columns are not feasible due to the data sequence and dynamic requirements of the application Feasible solutions will include worksheet functions and formulae for use within the functions, as well as instructions on (or pointers to instructions on) how to create my own set of functions Does anybody know why this capability is so elusive in standard Excel Thanks in advanc Bobb Other than VBA, u...

Problem counting
Hello All I have a table called [locums sessions] which includes the fields 'locdate' and 'locname' and key (autonumber) field 'locserial'. 'locname' is the name of a locum doctor, and 'locdate' is the date on which that doctor worked a session. I want a query that will return ALL the records corresponding to the doctors who are working one or more session today - i.e. where 'locdate' = Date(). e.g. if there is a record where 'locname' = "Dr Green" and 'locdate' = today's date, I want the query results to inclu...

count coloured cells in a range. Specific colour Red, Green, Blue
Hi I have filled in some cells with different colours, using the fill tool Red Blue Green I need a formula which will calculate each Red cell in a given range? I need a formula which will calculate each Blue cell in a given range? I need a formula which will calculate each Green cell in a given range? Again the range I need will be two different ranges. E.g A1:B5 and D1:D5 I look forward to your response Hi Sizz, See reponse in Programming. --- Regards, Norman ...

How to combine using COUNT "URGENT"
Example: I want to be able to count how many are 0_F from Column A and III fro Column D. I can't figure how to enter the function correctly. Th answer should be 2. I don't know how to combine information from different ranges. =COUNT(a1:13,"0_F") and =COUNT(D1:D13,"III") 0_F 1 3 IV 0_F 1 3 III 1 3 IV 0_F 1 3 II 1 3 III 0_F 1 3 II 0_F 2 3 I 0_F 1 3 II 0_F 2 3 0_F 1 3 III 0_F 2 3 0_F 1 3 I 0_R 1 3 II -- Message posted from http://www.ExcelForum.com Hi try =SUMPRODUCT(--(A1:A13="0_F"),--(D1:D13="III")) >-----Original Message----- >E...

conditional counting #2
For the following table: Apple gsmith1 bad Banana dwarf good Apple washin1 bad Apple gsmith1 good I'd like get a total count of all only bad apples. How would I go about it? Thanks in advance! Ridimz Hi =SUMPRODUCT((NameRange="Apple")*(ConditionRange="bad")) where NameRange and ConditionRange are references to ranges (columns) with data. The formula returns the count of rows with both conditions filled at same time. When you have also a column where quantities of fruits are entered, you can also summarize quantities - the for...

how do i count text and display it as text plus the # times it hap
PromotionName UnitSales ChannelName Quarter Boxing Day 1200 Richard 1 1 Boxing Day 1300 Will 2 2 Boxing Day 1600 Will 3 3 Boxing Day 1300 Will 3 4 I want to know how many times Boxing day occured as well as displaying the Text "Boxing Day" I tried SumProduct Function I tried CountIF and combination I tried Counta none of them work ="Boxing Day occurs "&COUNTIF(A:A,"Boxing Day")&" times" -- HTH Bob Phillips (re...

Get count of records for a particular month and year
I have a column with date records and I need to find out how many records/month/year. can anyone help me? Thanks. Select your column of date records, then use Data | Pivot table. Click through to the end, then drag the date field button to both the row field and the data field. Then use the Pivot Table button on the pivot table commandbar, and choose group. Then select month or year, and you will get a count of dates within each time period. HTH, Bernie MS Excel MVP "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:3381543A-6880-4F38-8F88-7DC6BBFD4E...

count within a range
Hi, I've got a column with a list of times and want to calculate the number of instances in the list that fall within 12 hours. ie. In the table below C6 should show the number of cells in column B that are within +/-12 hours of B6 A B C 1 Delivery Time No. Deliveries within 12 Hours 2 6/01/2004 0:01 3 7/01/2004 0:01 4 13/01/2004 0:01 5 28/01/2004 0:01 6 1/02/2004 1:43 7 1/02/2004 3:05 8 1/02/2004 5:54 9 1/02/2004 8:50 10 3/02/2004 3:07 11 3/02/2004 18:48 Many thanks in advance for your help. =COUNTIF(B:B,">"&B6-0.5)-COUNTIF(B:B,"&g...

count the number of caracters in a cell
How can I count the number of caracters including spaces in a microsoft excel worksheet See response in other thread in this newsgroup... In article <1E9BD85A-556F-444E-BFEA-92A4303D98F5@microsoft.com>, Jan <Jan@discussions.microsoft.com> wrote: > How can I count the number of caracters including spaces in a microsoft excel > worksheet =len(a1) "Jan" escreveu: > How can I count the number of caracters including spaces in a microsoft excel > worksheet ...