Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Count amount of cells equal to "1" in column if adjacent cell in row is equal to "1"

  1. #1
    Join Date
    6th April 2013
    Location
    New Zealand
    Posts
    3

    Count amount of cells equal to "1" in column if adjacent cell in row is equal to "1"

    Hi there folks,

    I've had an excel formula question which I've been trying to answer for a while now without much success so here's hoping!

    We're running a scientific trial which has two replicated treatments, coded either 1 or 0, and these are listed in a column in an alternating fashion (1,1,1,0,0,0,1,1,1)

    In 12 columns alongside there is monthly data for each replicate. Here's a picture of how it looks (The above description is quite hard to picture I guess)

    http://earthjanitor.com/wordpress/wp.../04/excel1.png

    Sorry I couldn't embed it in this message because it's too large.

    As you will see I've already used a SUMPRODUCT formula to sum all the cells containing various numbers if their adjacent treatment code is "1" or "0".

    I now need to count the number of cells in a particular month that contain any one of those various numbers and if their adjacent treatment code is "1" or "0".

    I need to populate the summary tables shown in this image below:

    http://earthjanitor.com/wordpress/wp.../04/excel2.png

    You'll see that the left, orange monthly tally is for lure results, so treatment code "1".

    For the first cell in that matrix I need to COUNT all cells in column F (from the first image) that equal 1 and have an adjacent treatment code of "1" in Column E (first image).

    Hope this explanation isn't too hazy. Please let me know if I can provide further clarification on anything.

    Thanks for any help, it's greatly appreciated!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count amount of cells equal to "1" in column if adjacent cell in row is equal to

    Hi ocokev

    Welcome to Ozgrid

    You have worked out how to upload files. Excellent. Pictures a almost useless from the development end. No one wants to recreate your data. So if you could cut that page into a fresh workbook, save it and upload it we can test the formula on your real data.

    Take care

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th April 2013
    Location
    New Zealand
    Posts
    3

    Re: Count amount of cells equal to "1" in column if adjacent cell in row is equal to

    Hi there Smallman,

    Fair call, I'll have to upload the entire file because there are a lot of formulae equalling cells on other worksheets and so on.

    The pictures here are worksheets "Data tabulation" and "Separated Data".

    The uploader here is returning an IO error when I try to upload something, so I'll host it on my server for download .

    http://earthjanitor.com/wordpress/wp.../04/stoats.xls (850kb)

    Thanks again everyone

    Kev

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count amount of cells equal to "1" in column if adjacent cell in row is equal to

    Hi Kev

    That is quite a spreadsheet. I advise that you read the Golden Rules for spreadsheet design at the top of the page. This formula in L8 of the Data tabulation sheet is priceless!!!!!;

    =SUMPRODUCT((E22:E319=1)*(F22:F319=1))+SUMPRODUCT((E22:E319=1)*(G22:G319=1))+SUMPRODUCT((E22:E319=1)*(H22:H319=1))+SUMPRODUCT((E22:E319=1)*(I22:I319=1))+SUMPRODUCT((E22:E319=1)*(J22:J319=1))+SUMPRODUCT((E22:E319=1)*(K22:K319=1))+SUMPRODUCT((E22:E319=1)*(L22:L319=1))+SUMPRODUCT((E22:E319=1)*(M22:M319=1))+SUMPRODUCT((E22:E319=1)*(N22:N319=1))+SUMPRODUCT((E22:E319=1)*(O22:O319=1))+SUMPRODUCT((E22:E319=1)*(P22:P319=1))+SUMPRODUCT((E22:E319=1)*(Q22:Q319=1))

    It can be replaced by the following.

    =SUMPRODUCT(($E$22:$E$319=1)*($F$22:$Q$319=1))

    This should give you an idea of what is possible with the rest of your model.

    Take it easy

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th April 2013
    Location
    New Zealand
    Posts
    3

    Re: Count amount of cells equal to "1" in column if adjacent cell in row is equal to

    Thanks for taking a look Smallman .

    That sumproduct tip was very useful and embarrassingly obvious! It must've been a long day at work hehe.

    In a similar fashion, I've also found a simple solution to what I'm trying to do:

    =COUNTIFS('Data tabulation'!$E$22:$E$319,1,'Data tabulation'!$F$22:$F$319,1) for the first cell of the first separated data tally table, and then just changing the column letters for the different months, different result codes for the different trap outcomes and so on.

    OR

    =SUMPRODUCT(('Data tabulation'!$E$22:$E$319=1)*('Data tabulation'!$F$22:$F$319=1)) for Excel 2003 and prior compatibility

    Thanks again,
    Kev

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count amount of cells equal to "1" in column if adjacent cell in row is equal to

    You are most welcome Kev.

    See you next time.

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: February 14th, 2013, 00:32
  2. Replies: 5
    Last Post: September 13th, 2012, 00:37
  3. Replies: 6
    Last Post: June 21st, 2011, 03:49
  4. Replies: 2
    Last Post: August 27th, 2005, 18:19
  5. CountIf does not count ">" or "<" on linked cells
    By blitzz_uk in forum EXCEL HELP
    Replies: 4
    Last Post: April 5th, 2005, 20:50

Bookmarks

Posting Permissions

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