Counting Individuals NOT Occurrences with than one criteria

I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as 
follows:

    A               B               C                 D               E      
          F
1  Harm Red   Fully Ach    Abstinence   Partly Ach   Harm Red   Fully Ach
2  Harm Red   Fully Ach    Harm Red    Fully Ach
3  Harm Red   Partly Ach
4  Abstinence  Not Ach      Harm Red    Partly Ach
5  Abstinence  Partly Ach
6  Harm Red   Fully Ach    Harm Red    Partly Ach
7  Harm Red   Fully Ach

Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 
'Harm Red' and 'Fully Ach'.

I need to be able to count individuals with at least one 'Harm Red' with a 
'Fully Ach'.  So in the example above there are 4 individuals (out of 7) with 
at least one 'Harm Red' and 'Fully Ach' type of pairing even though there are 
6 occurrences of this type of pairing.

I definitely need to be able to know how to arrive at the answer 4 through 
Excel Worksheet Functions.

Could anybody help please!!

Thanking you in advance

All the best

Roy
0
Utf
12/9/2009 1:37:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
488 Views

Similar Articles

[PageSpeed] 35

Hi,
try

=SUMPRODUCT((A1:A7="Harm Red")*(B1:B7="Fully Ach"))

"Royegg" wrote:

> I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as 
> follows:
> 
>     A               B               C                 D               E      
>           F
> 1  Harm Red   Fully Ach    Abstinence   Partly Ach   Harm Red   Fully Ach
> 2  Harm Red   Fully Ach    Harm Red    Fully Ach
> 3  Harm Red   Partly Ach
> 4  Abstinence  Not Ach      Harm Red    Partly Ach
> 5  Abstinence  Partly Ach
> 6  Harm Red   Fully Ach    Harm Red    Partly Ach
> 7  Harm Red   Fully Ach
> 
> Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 
> 'Harm Red' and 'Fully Ach'.
> 
> I need to be able to count individuals with at least one 'Harm Red' with a 
> 'Fully Ach'.  So in the example above there are 4 individuals (out of 7) with 
> at least one 'Harm Red' and 'Fully Ach' type of pairing even though there are 
> 6 occurrences of this type of pairing.
> 
> I definitely need to be able to know how to arrive at the answer 4 through 
> Excel Worksheet Functions.
> 
> Could anybody help please!!
> 
> Thanking you in advance
> 
> All the best
> 
> Roy
0
Utf
12/9/2009 1:50:01 PM
Hi Eduardo,

Thanks for your reply.

I've tried SUMPRODUCT type formalas like this before but it comes up with 
occurrences rather than the number of individuals.  I did try your version 
and added several other 'pairings' but unfortunately it only seemed throw up 
occurrences. 

All the best

Roy

"Eduardo" wrote:

> Hi,
> try
> 
> =SUMPRODUCT((A1:A7="Harm Red")*(B1:B7="Fully Ach"))
> 
> "Royegg" wrote:
> 
> > I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as 
> > follows:
> > 
> >     A               B               C                 D               E      
> >           F
> > 1  Harm Red   Fully Ach    Abstinence   Partly Ach   Harm Red   Fully Ach
> > 2  Harm Red   Fully Ach    Harm Red    Fully Ach
> > 3  Harm Red   Partly Ach
> > 4  Abstinence  Not Ach      Harm Red    Partly Ach
> > 5  Abstinence  Partly Ach
> > 6  Harm Red   Fully Ach    Harm Red    Partly Ach
> > 7  Harm Red   Fully Ach
> > 
> > Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 
> > 'Harm Red' and 'Fully Ach'.
> > 
> > I need to be able to count individuals with at least one 'Harm Red' with a 
> > 'Fully Ach'.  So in the example above there are 4 individuals (out of 7) with 
> > at least one 'Harm Red' and 'Fully Ach' type of pairing even though there are 
> > 6 occurrences of this type of pairing.
> > 
> > I definitely need to be able to know how to arrive at the answer 4 through 
> > Excel Worksheet Functions.
> > 
> > Could anybody help please!!
> > 
> > Thanking you in advance
> > 
> > All the best
> > 
> > Roy
0
Utf
12/9/2009 2:18:01 PM
Hi Roy,

I would create some additional columns; One for each of the criteria Harm 
Red   Fully Ach etc and use the names as column headers. In your example they 
would be columns G to K with the column headers
Harm Red, Fully Ach, Partly Ach, Not Ach, Abstinence

In cell G2 insert =COUNTIF($A2:$F2,G$1)
Note absolute ($signs) on the columns for $A2:$F2 and absolute on the row 
for G$1.
Copy the formula across to column K and down for the length of your data.

Now to count the Harm Red and Fully Ach across the row.   
In Cell L2 insert =COUNTIFS(G2,">0",H2,">0")

Copy this formula down for the length of your data.
Sum column L for the total answer.

You can add additional columns like L for counts of other combinations.

-- 
Regards,

OssieMac


0
Utf
12/9/2009 2:22:02 PM
Hi there.
Did you even give a try to my suggestion in your original post? If not, 
please, see the link below:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.worksheet.functions&mid=2ebe2ed6-c4ff-4fe6-9cc2-17fb83f0c503&sloc=en-us

Regards,
Otávio

"Royegg" wrote:

> I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as 
> follows:
> 
>     A               B               C                 D               E      
>           F
> 1  Harm Red   Fully Ach    Abstinence   Partly Ach   Harm Red   Fully Ach
> 2  Harm Red   Fully Ach    Harm Red    Fully Ach
> 3  Harm Red   Partly Ach
> 4  Abstinence  Not Ach      Harm Red    Partly Ach
> 5  Abstinence  Partly Ach
> 6  Harm Red   Fully Ach    Harm Red    Partly Ach
> 7  Harm Red   Fully Ach
> 
> Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 
> 'Harm Red' and 'Fully Ach'.
> 
> I need to be able to count individuals with at least one 'Harm Red' with a 
> 'Fully Ach'.  So in the example above there are 4 individuals (out of 7) with 
> at least one 'Harm Red' and 'Fully Ach' type of pairing even though there are 
> 6 occurrences of this type of pairing.
> 
> I definitely need to be able to know how to arrive at the answer 4 through 
> Excel Worksheet Functions.
> 
> Could anybody help please!!
> 
> Thanking you in advance
> 
> All the best
> 
> Roy
0
Utf
12/9/2009 11:01:41 PM
Hi OssieMac,

Thank you for your help with this problem - it definitely works a treat!!

I should have thought to create new variables like you would in SPSS but the 
beauty of Excel is that it all becomes automatically updated with the overlay 
of new raw data - something which SPSS does not do as you always seem to have 
re-run variables.

Thanks once again

All the best

Roy

"OssieMac" wrote:

> Hi Roy,
> 
> I would create some additional columns; One for each of the criteria Harm 
> Red   Fully Ach etc and use the names as column headers. In your example they 
> would be columns G to K with the column headers
> Harm Red, Fully Ach, Partly Ach, Not Ach, Abstinence
> 
> In cell G2 insert =COUNTIF($A2:$F2,G$1)
> Note absolute ($signs) on the columns for $A2:$F2 and absolute on the row 
> for G$1.
> Copy the formula across to column K and down for the length of your data.
> 
> Now to count the Harm Red and Fully Ach across the row.   
> In Cell L2 insert =COUNTIFS(G2,">0",H2,">0")
> 
> Copy this formula down for the length of your data.
> Sum column L for the total answer.
> 
> You can add additional columns like L for counts of other combinations.
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
12/10/2009 12:47:02 PM
Reply:

Similar Artilces:

Mailstore
Hi, I am looking to move half my mail users on to a new mailstore, this basically will reflect users in the UK and US. We currently have a single mailstore, I wish to move all our US users to a separate mailstore. However before I do this I need to work out what size the new US mailstore will be. I believe that if there are emails in the store for recipients both in the US and UK a copy of that mail will reside on both mailstores, once the recipient is moved to the new mailstore. However if the mail on the store is between two US users and they are both on the new US store only one copy of ...

Works on one computer, but not another
I'm working on a vba enhanced macro that publishes a pdf to the desktop. I'm in an office with about 10 computers, all using MSOffice 2007. So far, it works on 3 of 5 computers. Here is the sample of code that the debugger flags: ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="C:\test\test.pdf" Openafterpublish:=False The error message is "Runtime error #5, invalid procedure call or argument" I just checked and both computers were not updated with the Office SP2. I haven't tried it since both have b...

Copy data from one workbook to another
Here is what I want to do: For R = 2 To numRows - 2 Step 3 For C = 6 To 26 .Cells(R, 5) = myArray(1) .Cells(R, 3) = myArray(2) .Cells(R, 2) = myArray(3) .Cells(1, C) = myArray(4) .Cells(R, C) = myArray(5) .Cells(R + 1, C) = myArray(6) .Cells(R + 2, C) = myArray(7) ' Switch to workbooks("Database").Worksheets("Database") ' copy array into columns 1 thru 7 of first empty row starting on row 2 &...

transfer data from one excel sheet to another
Hello, We currently receive analysis data from a contractor on an excel spreadsheet that contains more information than we need. I would also like to rearrange the data into another excel spreadsheet that is easier for me to look at the data. We reenter the data into a paradox database( Yes, it is archaic) that is very difficult to change the format. Is there a way to automatically rearrange and transfer the data to another excel spreadsheet via a macro, xml, etc? My experience with excel is limited and I only know basic functions. Thank You. -- Exceloficio ------------------------------...

How do I save just one page of a Publisher calendar elsewhere?
I have a 2009 template calendar that I would like to be able to save each month to my desktop (or someplace else) so that I can save just that page as a PDF and email it. Any suggestions? You can create a one page calendar and save it. The following month open the calendar, change the date range. If you have already created the calendar with special days and events you can delete 11 months, save with a new name the single monthly page. Or copy/paste the month to a new publication. Keep in mind the color scheme, it will change when you copy/paste. You can apply the color scheme from the...

Lookup data based on 3 criteria
Hi all! I need help with a problem I'm having with a lookup. I was wondering if I can lookup a text entry from a list based on 4 criteria. Cells E3, E4, E5, and E6 will contain user selected values which would in turn return a corresponding value from a list based on what's in the aforementioned cells. This value will be stored in cell A10. The lookup list is on another sheet in the Excel workbook. I've been ableto do it with 2 criteria, but can't make it work with 4. Please help! Presumably you have columns in your table on the other sheet which correspond to the user's ...

Datagridview confused on row count
I have a windows form with the datagridview that displays data from a single table. The underlying data can be updated/deleted from another component. In our case, we raise an event when any underlying changes happen. We then have been trying to refresh our grid without much luck. We use: this.DataSet.Offers.Clear(); this.offersTableAdapter.Fill(this.DataSet.Offers); this.offersBindingSource.ResetBindings(false); this.offersDataGridView.Refresh(); We have tried multiple variations but the underlying data is fairly small. Unfortunately, if a row is deleted often the grid will t...

Adding several Excel files into one
I have about three separate Excel forms that I want to put into one file but on their own spread sheet. Can this be done? If so, can it be done with me having to resize the cells. I've tried this before with cut and paste but I have to resize the cells. Too much for allot of cells. Thanks in advance First do a PASTE SPECIAL and select Column Widths then do a simple paste. "Kenny Dee" wrote: > I have about three separate Excel forms that I want to put into one file but > on their own spread sheet. Can this be done? If so, can it be done with me > having to resi...

How to Create Multiple Email accounts for One user???
Hi, I am newbie with ExchangeSrv, with some users of my SBS2003 I need to create 2 email accounts with their respective mailboxes �How can I do this? if it is possible Thanks. you will need to create separate user accounts...after you create them, you can grant "Full Mailbox Access" and "Send as" rights to the other user...if you want to disable the new account, you can also set "Associated External Account" and "Full Mailbox Access" to "Self", then disable that account...then the user that needs to use them both will be able to... "R...

copy and paste one file into several different folders at the same
Is it possible to copy and paste one file into several different folders at the same time? Sorry, still no. james wrote: > Is it possible to copy and paste one file into several different folders at > the same time? ...

OWA 2003 works for all sites but one (401.5 front end, 401.2 back
Sorry for the cross post but there is not much traffic on microsoft.public.exchange.setup We have a classic front end back end installation protected by ISA. We have just installed a new site and cannot connect to mailboxes there via OWA - MAPI works fine and there are no errors or warnings in the Event logs. The IIS log on the front end says 401.5 and the backend IIS log shows 401.2. If we connect directly to the backend after providing credentials we are able to see the mailbox. Other users with identical configurations from other sites are working fine. We were scratching our he...

Find next record according to specific criteria
I have a field on a form that has a checkbox. I want to make a button that will go to the next record that doesn't have the checkbox checked (I don't want to filter the records, just to advance to the next unchecked record). What code do I use to accomplish this? You can use the FindNext method to do that: With Me.RecordsetClone .FindNext "[CheckBoxFieldName] = 0" If .NoMatch Then MsgBox "No More UnChecked Records" Else Me.Bookmark = .Bookmark End If End With Note CheckBoxFieldName is the name of the...

Counting instances of a time in cells with date AND time.
Hi all, I'm kind of new and in a bind. I have one worksheet that has column of cells that have a date and time. For example. A 1 Time_Entered 2 4/5/04 9:00:00PM 3 4/6/04 7:40:00AM ... And it goes down. Now, in another worksheet (but you can assume th same worksheet for explaining's sake), I have to count how many time Column A had a time of 7:00:00AM. Any time between 7:00 and 7:59, i other words. I don't know how to count it with the date also in th way. I CAN'T make a new column that has HOUR(A:A) however, that's wha ...

occurence count
I've got a row representing 31 days in a month. If a worker calls in sick, the hours missed goes in that cell. That could be any number up to 8 hours. That absence may carry into many days. I need to calculate the number of occurences. How many times did they call in sick that month? Assume worker calls in sick: 3rd,4th,5th Assume worker calls in sick: 8th Assume worker calls in sick: 21st,22nd That's 3 occurences. What's the simplest formula to show 3 occurences? thanks! One way: Assume A1:AE1 are days of the month A2:AE2 is where you enter the hours missed (if any) oth...

Frond Production Count
Hi - need some assistance here .. I have a table that has a date column, number of fronds, palm number, block number The date is for when the recording date of number of fronds for each palm is done. I would like to create a query that picks up a palm (there can be up to 16 palms in a block) in a block and calculate the avg of frond between the date of the first recording and the second recording. Do I use a cross tab query or a normal query - Please help thanks, On 13 Apr 2007 17:45:11 -0700, "c8tz" <ccholai@gmail.com> wrote: >Hi - need some assistance here .. >...

Saving multiple worksheets as individual Web pages
I receive a workbook each month with 15 worksheets. I need to save each individual worksheet as a Web page to post to our Intranet, so I end up having to Save As Web page 15 times. Is there an easier or more automated way? Thanks for your help. record saving one as a macro then edit it to see the code for how to do it, then play around with that : for x=1 to 15 sheets(x). then the code for the saving as next x somehting like that. >-----Original Message----- >I receive a workbook each month with 15 worksheets. I >need to save each individual worksheet as a Web page to...

How does one copy subtotal rows?
How does one copy subtotal rows? -- Since my access to NNTP is limited, a copy of your reply to ALEXANDEReBARNEs@Yahoo.Com is especially appreciated. Alexander One usually selects the rows he wishes to copy, Then hits <F5>, Then, in the "GoTo" window clicks on "Special", Then, in the "GoToSpecial" window clicks on "VisibleCellsOnly", then <OK>. Now, right click in that selection and choose "Copy", Then navigate and paste to "wherever". -- HTH, RD ------------------------------------------------------------------...

Converting a Word document into an Excel one
I have a Word document with the following repeating layout. HEADING 1 124 HEADING 2 Monster, but in reality, occular, but, twenty four horses in kindness of love of the saint of buddha. Men in green trousers up the valley discovered the mouse of cordless. Wireless. 1245 Hi. HEADING 3 $123,425-$1,566,109 HEADING 4 123 Fake St. Middle of Nowhere, Empty Country Then a new entry starts, and so on. Not all entries have all the headings. E.g., a few might be missing heading 2 or 4. There are also more than 4 headings, but that is irrelevant. How could I convert this mishmash of types of cells (n...

Pass infor from one form to another
Hi all Here is my dilemma I am trying to create a form that the user can select a category and either type in a free text description in the next cell of a continuous form or on selecting certain categories they will have a pre defined pick list that the user must select a single item from a list presented to populate the next cell. I have got to this so far:- I have a Continuous form with a drop down box to allow the user to select the required category. What I am looking at doing is depending on the category selected there are two outcomes either the category is selected and the us...

adding a count column
I need to add a column that gives a unique number to each row (except the first or label row) in an excel database. From #1 on. How do I do that? Thanks. Jeff Select an empty column................A for example. In A2 enter 1 Right-click on the fill handle and drag down as far as you wish. Release button and "Fill Series" Gord Dibben MS Excel MVP On Wed, 24 Nov 2010 14:50:50 -0500, "Jeff@nospam.invalid" <Jeff@nospam.invalid> wrote: >I need to add a column that gives a unique number to each row (except >the first or label row) in an excel databa...

Statistic: Problem with two one sided t-test
Dear all, I have a problem with some statistic calculation. I have 2 series of measure: the first column is the test series and the second column if the reference series. I have to calculate the standard 90% confidence intervals of the ratio test/reference (T/R) and I have to answer to this question: the products were considered bioequivalent if the difference between two compared parameters was found statistically insignificant (P < 0.05) and 90% confidence intervals for these parameters fell within 80-120%. I know the result for these series, but I have more to calculate: 90% CI : 88.1-11...

20+ different Excel documents open at one time
We have a user when he open Excel for the first time, 20+ different Excel documents opens up. User must close all unwanted documents, leaving excel open before he can then continue to work. User is on W2K using Office 2000 Pro. Prior to that, we had installed Office 2000 Prem. We did a completed uninstall of the Prem addition and then a fresh load of Pro, but the problem is still there. Hi Check out Tools>Options..General first And see if the "At startup: open all files in" is empty -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "tb...

M05 single category list haters: one thing not to try
The obvious registry hack really makes a mess of the categories setup in your file. Don't go there. The reg hack did not cause the problem I saw--that was pure M05 maliciousness... (Moving Food:Groceries to Groceries and Food:Dining Out to Dining Out.) I'll try to go back and play with the reg hack again. I think it does exactly nothing. "Dick Watson" <littlegreengecko@mind-enufalready-spring.com> wrote in message news:%23knflAenEHA.4004@TK2MSFTNGP10.phx.gbl... > The obvious registry hack really makes a mess of the categories setup in > your file. Don't ...

default printer setup for individual worksheet only
Is it possible to set different default printer settings for individual workbooks? ie- I have one workbook that contains twelve worksheets...I want these to print by default on 8 1/2 x 14 legal paper. All other workbooks, I want to print standard 8 1/2 x 11. I would like to be able to do this without having to open file/page setup/etc. each time. Thanks, Al Al The page setup is saved with the workbook. For that particulae workbook you should be able to setup as you wish and save it. Other workbooks would take the default settings which are set with th...

Counting records with in a range of a feild
I'm having problems trying to automate the count of records that have a date in a feild that lies between the range of txtStartDate and txtEndDate. Any tips or suggestions? ...