#### Count occurrances with conditions

```I am trying to count in 3 columns.   Column A will have entries of "B
or "S" and columns B and C will have "1" or "0".

A               B           C
B                0           1
B                1           0
B                1           1
S                1            1

I want to count how many times column C has a 1 with a B or S.  That i
no problem, I can do that.  I also want to count the number of time
column B has a 1 with a B or S.  That is no problem, I can do that.
However, the third count I want to count is when a 1 appears in bot
column B and C, I want ONLY to count the occurrance in column C.  I CA
NOT figure that out.

These are the formulas I am using for single occurrances.
=SUM(IF(C18:C38="B",IF(R18:R38>0,1,0)))
=SUM(IF(C18:C38="S",IF(R18:R38>0,1,0)))

I enter them with SHIFT/CTRL/Enter

Can anyone help?  Thanks!!

--
WM
-----------------------------------------------------------------------
WMO's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2606
View this thread: http://www.excelforum.com/showthread.php?threadid=39403

```
 0
8/8/2005 9:19:14 PM
excel 39879 articles. 2 followers.

5 Replies
957 Views

Similar Articles

[PageSpeed] 37

```=SUMPRODUCT(--(C18:C38="B"),--(R18:R38=1),--(S18:S38=1))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"WMO" <WMO.1tguef_1123538706.037@excelforum-nospam.com> wrote in message
news:WMO.1tguef_1123538706.037@excelforum-nospam.com...
>
> I am trying to count in 3 columns.   Column A will have entries of "B"
> or "S" and columns B and C will have "1" or "0".
>
> A               B           C
> B                0           1
> B                1           0
> B                1           1
> S                1            1
>
> I want to count how many times column C has a 1 with a B or S.  That is
> no problem, I can do that.  I also want to count the number of times
> column B has a 1 with a B or S.  That is no problem, I can do that.
> However, the third count I want to count is when a 1 appears in both
> column B and C, I want ONLY to count the occurrance in column C.  I CAN
> NOT figure that out.
>
> These are the formulas I am using for single occurrances.
> =SUM(IF(C18:C38="B",IF(R18:R38>0,1,0)))
> =SUM(IF(C18:C38="S",IF(R18:R38>0,1,0)))
>
> I enter them with SHIFT/CTRL/Enter
>
> Can anyone help?  Thanks!!!
>
>
> --
> WMO
> ------------------------------------------------------------------------
> WMO's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26065
> View this thread: http://www.excelforum.com/showthread.php?threadid=394033
>

```
 0
bob.phillips1 (6510)
8/8/2005 10:36:14 PM
```Trying to reach Bob Phillips who logged on as a guest.  I appreciat
your response but it did not resolve my issue.  I have now attached
portion of the spreadsheet with more detail.

If you (or anyone else) can make an additional response, I would
greatly appreciate it.  Thank

+-------------------------------------------------------------------
|Filename: excel forum.zip
|Download: http://www.excelforum.com/attachment.php?postid=3678
+-------------------------------------------------------------------

--
WM
-----------------------------------------------------------------------
WMO's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2606
View this thread: http://www.excelforum.com/showthread.php?threadid=39403

```
 0
8/9/2005 2:45:31 AM
```I didn't sign on to the forum as a guest, I didn't come to the forum at all.
Your forum questions are automatically forwarded to the Microsoft Public
newsgroups which is where I see them.

Anyway, try this alternative

=SUMPRODUCT(--(C17:C37="B"),--(Q17:Q37=1),--(R17:R37<>1))

for bulk items

--
HTH

Bob Phillips

"WMO" <WMO.1th8ad_1123556707.0352@excelforum-nospam.com> wrote in message
news:WMO.1th8ad_1123556707.0352@excelforum-nospam.com...
>
> Trying to reach Bob Phillips who logged on as a guest.  I appreciate
> your response but it did not resolve my issue.  I have now attached a
> portion of the spreadsheet with more detail.
>
> If you (or anyone else) can make an additional response, I would
> greatly appreciate it.  Thanks
>
>
> +-------------------------------------------------------------------+
> |Filename: excel forum.zip                                          |
> |Download: http://www.excelforum.com/attachment.php?postid=3678     |
> +-------------------------------------------------------------------+
>
> --
> WMO
> ------------------------------------------------------------------------
> WMO's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26065
> View this thread: http://www.excelforum.com/showthread.php?threadid=394033
>

```
 0
bob.phillips1 (6510)
8/9/2005 8:08:58 AM
```Bob.....thanks very much.  That works perfectly.  Seems so simple once
see it.  I suppose everything is simple when you understand. I ha
tried, at least, 4,000 variations of my knowledge.....nothing worked.
I was getting totally frustrated.

I found the Excel forum site.  Had never used it before so was no
aware of how it worked.  Did not realize you came from "somewher
else", but am I glad you did!

You have impressed everybody who sits around me.  We all had trie
something but it (they) never worked.  I need to brush up on SUMPRODUC
and will certainly do that.

Again, thanks VERY MUCH for your help.  You, indeed, saved the day fo
us

--
WM
-----------------------------------------------------------------------
WMO's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2606
View this thread: http://www.excelforum.com/showthread.php?threadid=39403

```
 0
8/9/2005 12:48:27 PM
```If you want to brush up on SUMPRODUCT, take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

"WMO" <WMO.1ti05p_1123592833.7731@excelforum-nospam.com> wrote in message
news:WMO.1ti05p_1123592833.7731@excelforum-nospam.com...
>
> Bob.....thanks very much.  That works perfectly.  Seems so simple once I
> see it.  I suppose everything is simple when you understand. I had
> tried, at least, 4,000 variations of my knowledge.....nothing worked.
> I was getting totally frustrated.
>
> I found the Excel forum site.  Had never used it before so was not
> aware of how it worked.  Did not realize you came from "somewhere
> else", but am I glad you did!
>
> You have impressed everybody who sits around me.  We all had tried
> something but it (they) never worked.  I need to brush up on SUMPRODUCT
> and will certainly do that.
>
> Again, thanks VERY MUCH for your help.  You, indeed, saved the day for
> us.
>
>
> --
> WMO
> ------------------------------------------------------------------------
> WMO's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26065
> View this thread: http://www.excelforum.com/showthread.php?threadid=394033
>

```
 0
bob.phillips1 (6510)
8/9/2005 1:25:04 PM
 Reply:

Similar Artilces:

Creating A Chart that counts repitition (don't know name)
So, I feel like a moron not knowing this. But I must have had a brain freeze for the last week. I have a tableset of about 300 rows in Excel 2007 with one column being a name and the other being a year. I want to create a graph of the years. IE Y Axis = number of times a certain year appears in the tableset column and the x axis being the years between (year k and year m). Sounds like a histogram. First you need to compile the data, then make a chart of the compiled data. Here's how: http://peltiertech.com/Excel/ChartsHowTo/ConsolidateData.html - Jon ------- Jon Peltier, Microso...

Creating a printed report containing multiple counts
Hi All, Been trawling around for a solution to this problem. The problem is pretty straightforward, yet surprisingly complicated to solve. I have a statistical report that needs creating when demanded by the user. The user enters a date range on the form in 2 text boxes and the values are passed into queries(?). The stats report should then contain a load of count statistics for how many records have been received/rejected/exported etc. in that date range. The counts also have different conditions - imported records for instance need to fit the date range entered by the user, but f...

Counting Results of Formulas
I have a column in a spreadsheet that contains the result of a a lookup function (refering to values in another spreadsheet) and displays a blank field if the lookup does not find a match in the second spreadsheet. I need to count how many matches are found (or not found). But when I use the count function, it seems to be counting the formula and not the result of the formula. Does this make sense? Example: Formula of cells B10:B1500 = IFERROR((VLOOKUP(C45,'[Completed Property List.xls]Sheet1'!\$A\$2:\$B\$10000,2,FALSE)),"") - displays the contents of ma...

Word count
Word 2003 Is there a way of counting the total words in a document, INCLUDING footnotes? hank you JB Please ignore post. I found the answer! JB "JB" <open@closed.com> escreveu na mensagem news:u0AuviooKHA.2076@TK2MSFTNGP05.phx.gbl... > Word 2003 > > Is there a way of counting the total words in a document, INCLUDING > footnotes? > > hank you > > JB > ...

Conditional format #9
Is there anyway of doing more than 3 conditional formats as excel will only allow 3. Hi Cass! Conditional formatting is limited to three conditions. However you do have the default format that applies where no condition is satisfied. You also have the possibility of different formats for (usually) positive, negative and zero. But in most cases where you need more than three formats for your conditions, you will need to resort to VBA. For this, see: Dave McRitchie: http://www.mvps.org/dmcritchie/excel/event.htm#case However, if you are doing this for mainly presentation purposes, my own...

Copying Conditional Formatting #2
I am currently using Excel 2003. I have set a cell to have conditional formatting based on the result of another cell. How can I copy this logic to another cell - with the logic in the cells changing (like a formula)? For example: color cell C4 red if A4 is 10 -> copy this to C5 with dependency on A5, and etc... -> Basically, I want to color every cell in Column C red if the value in Column A is 10 without going into every cell in the column and making the dependency changes respectively. Thanks in advance. Deluth. Hi, When you setup the Conditional Formatting and select...

formula on counting
Hello, could someone please help me with a forumula. Im not sure what function to use to get this to work right. I need a formula that will count how many entries in column C say "CXL". But I only want it to count on the rows that have "John" in column A. Im not sure how to do this. Thanks! try =sumproduct((a2:a22="John")*(c2:c22="cxl")*1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Michael A" <MichaelA@discussions.microsoft.com> wrote in message news:1620B367-6FDF-4925-9F3D-ED2DF32D9AA9@microsoft.com... > He...

Conditions
First sorry for my English. May I know how to create a conditional function for this reason. I create a function in A1 cell using data validation function list option. After that I can select in A1 cell as 2000, 2001, 2002, 2003 like that. I typed in I column as I3=1 , I4=2 , I5=3 Now I want to prepare a condition in A3 cell If A3=2000 A3 = I3 and If A3=2001 A3 = I4 and If A3=2002 A3 = I4 and If A3=2003 A3 = I5 like that. Please help me to create this condition. Hi, Maybe this =CHOOSE(A1-1999,I3,I4,I5) Mike "Lahi" wrote: > First sorry for...

How can I place a condition on a list?
I have one column that has a list of 3 elements. I want to put a condition on another column to display a specific drop down list depending on what was input in the first column. Can I do this? I'm sure this is a more advanced way, but the poor man's solution would be... with only three elements, you have the ability to use conditional formatting on the list. I believe excel allows up to three conditions. Just set up the conditions to be If equals "element a" then this result and repeat for the next 2. I'm sure there is probably some heavy duty code u could do...

count
Hi Is it possible to count cells in a range than contains f.e. "/" or that begins with "/"? thx Hi try =COUNTIF(A1:A100,"*/*") or =COUNTIF(A1:A100,"/*") -- Regards Frank Kabel Frankfurt, Germany "masterphilch" <masterphilch@gmx.net> schrieb im Newsbeitrag news:clp4ov\$30b\$1@newshispeed.ch... > Hi > > Is it possible to count cells in a range than contains f.e. "/" or that > begins with "/"? > > thx ...

Conditional Formatting on more than "4" Conditions
Employees are in one column and their department is in another. This is for a multi-user attendance tracking system I'm trying to put together for my company. I'm going to creat a validation list with the departments in it. I want the user to be able to select the department in the dropdown under the department column and have it change the color of the font of the employees name in the other column. This will help upper management better distinguish departments in the master list. My problem is that I have 8 departments and Excel will only allow me to make 3 different conditions...

How do I count something that has two fields that must be true
I am working to count items in a spreed sheet that has several fields on it. I want to count how many items have an item number of "4016" and a price of "100". Both fields must equal true for it to count them. Thank you, Charles =SUMPRODUCT(--(B2:B100=4016),--(C2:C100=100)) -- HTH RP (remove nothere from the email address if mailing direct) "Charles" <Charles@discussions.microsoft.com> wrote in message news:EC3C74A7-BAB7-4F98-853B-F495C12B33F8@microsoft.com... > I am working to count items in a spreed sheet that has several fields on it. >...

Count the number of times a worksheet is viewed
Is there a simple macro that will show how many times a worksheet (not workbook) has been viewed? Thanks. Michael You could use a worksheet_activate event to add a number to a cell each time. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <MichaelRLanier@gmail.com> wrote in message news:53dc5190-1c16-45e9-837d-7fd6c129077f@v38g2000yqb.googlegroups.com... > Is there a simple macro that will show how many times a worksheet (not > workbook) has been viewed? Thanks. > > Michael Right click the worksheet tab, select 'View Code', c...

cell count of 'grouped' cells
Apologies if this is a 'dumb' question! I am receiving data that is layed out in groups. When I try to 'countif' with some of the group not showing, I still get a full row count, including those rows not showing. Basically, I receive attendance stats with every days attendance detailed, grouped into the weekly attendance per person. As I need the number of people, I am trying to count just those (the second group/layer), but when I try the countif(data,"*"), it counts all of the rows, including the sub rows giving the daily info. Please can someone help/explain w...

Conditional Format II
With Conditional Formating how do you?: Column A & B contain numbers. I set cell A1 with the Conditional Format to show red background if A1 is more than B1. I then used the Format Painter to set the balance of Col A with the same formating applied to cell A1. The problem is that when I use the "Format Painter" (on Col A) each of the cells in Col B references cell B1. I want each cell in Col B to reference its adjacent Col A cell. -- ALAN KAY 19 Dorian Way, San Rafael, Ca. 94901 PHONE (415) 454-5342 FAX (415) 453-2657 E-Mail: almkay@comcast.net in Cell A1, conditi...

Hiding Rows
HI all, I would like to have a macro that will hide rows based on their background color. That is, some rows (yellow) need to be completed only for add-on sales, while new sales (blue) require a different group. I know how to hide specific rows, but not how to make it conditional. Is it possible? Many thanks../RN Hi try something like the following: Sub hide_rows() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "A").interior.colorindex=3 Then Rows(R...

record count issue
I use recordcount to check my DAO.recordset is empty or not. The recordcount returns 1 when the resordset is empty. Are there any work around for this? Your help is great appreciated, "iccsi" <inungh@gmail.com> wrote in message news:80308099-9f06-42c9-bbc7-9f9f57948450@h21g2000vba.googlegroups.com... >I use recordcount to check my DAO.recordset is empty or not. > The recordcount returns 1 when the resordset is empty. > > Are there any work around for this? I've never heard of such a thing, and doubt your results. Please post your code. ...

Counting rows that fit within a range
I've got what seems to be a simple problem to do by hand (if it weren't for the fact that the table in question isn't exactly small). So I was hoping I could do it (somehow) in an Excel spreadsheet. What I have, is a column of numbers (to one decimal point), assumed to be sorted in descending order. I want to have another column (calculated by Excel) that will count how many rows have a larger value (in the first column) than it, but only up to a certain range (by adding a constant to the value of that row). For example, here's some data, with the first column supplied by ...

Conditional Formatting #10
2 things.... 1. - I have got a list that is linked to a cell - when a paticular item in the list is chosen I want the cell to be filled with a colour - i.e. Conditional Formatting - however how do I allow for more than 3 conditional formats - there are about 7 choices from the list. 2. - Linking from the above - I then want the rest of hte information in that row to be coloured the same as box in problem 1. I cannot seem to get the Conditional Formatting to work for this..? Any ideas..? Thanks Vis. You'll have to use some VBA code to do this. Add this to the worksheet module ...

Conditional Formating #14
Using conditional formatting, how can I blank a cell that has a formula that doesn't return a value... eg #DIV/0! Thanks Click "Formula Is", and enter this: =ISERR(A1) And choose the format to be White font. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bassett" <Bassett@discussions.microsoft.com> wrote in message news:96A8A842-7685-4086-A1E9-42C3A92310DF@microsoft.co...

COUNTIF? Use one column as condition to determine average of another
This may be beyond the scope of basic excel, but here goes. C4:C16 contains a list of times, or durations in this case. In D4:D16 each cell contains either a "yes" or "no" what I would like to do is average the "yes" times. So referenc D4:D16 as a condition to determine which times to average. I could just manually seperate the "yes" times and compute the average but there must be a way to do it as described above -- Message posted from http://www.ExcelForum.com On 5/8/04 6:43 PM, in article pacman2k4.15ylio@excelforum-nospam.com, "pacman2k4...

Excel Conditional Formatting?
Hi, I have our company IP address range in excel format. I want to do conditional formatting so I clearly see the different scopes. If I do conditional formatting so that 'If Cell', 'Is Between' '192.168.61.0' & '192.168.61.255' format colour as Red. Only a few of the IPs actually go red! i.e. 192.168.61.25 is red, but 192.168.61.27 isn't! Can anyone see why? Is this a bug? Cheers Ben Ben It works for me!! FYI I'm running XL2K and Win98SE Andy "Ben Blackmore" <bjblackmore@nospam.hotmail.com> wrote in message news:3f65cdb4\$0\$2...

Count by Date
I am trying to create a query that will capture how long a particular record has been in a queue. I have another query called Status of Item. This query has the following Fields: ItemId, Date Entered, and Queue. Now I need my new query to group the Queues and count the # of ItemId's by how many days from when it was entered. I need it to look something like this: Queue <10 10-20 21-30 31-40 >40 Grand Total Blue 4 6 0 0 2 12 Purple 8 2 ...

Count blanks by date
I have a table that has meter# , date, and usage in colums A, B and C. I use a formula =sumif(\$B\$11:\$B1000,"="[date],\$C\$11:\$C1000) to total up the amount of usage for each day. But what I want to do is count the number of blanks in column C for each date. So "if \$B\$11:\$B1000,"="[date] then countblank(\$C\$11:\$C1000)" is kind of what I'm shooting for but I can't wrap my brain around how to do it. I would like to report that on 4/1 we had 1,912 usage and 4 meters did not report (were blank). Thanks! Try =SUMPRODUCT(--(\$B\$11:\$B1000=[date]),-...

Conditional Sum Wizard: Error: "Randomized Numbers"
Why does the Conditional Sum Wizard sometimes give me the message "You range contains one or more entries using randomized numbers." I am not using randomized numbers. Thanks, Charlie Rowe Hi! Don't know the answer to your specific question but......... The Conditional Sum Wizard is very limited in it's ability and scope and NEVER produces an efficient solution. If you could provide an explanation of what you're trying to do someone will probably be able to help. Biff "Charlie Rowe" <CharlieRowe@discussions.microsoft.com> wrote in message new...