Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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
    sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

    Comment


    • #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

      Comment


      • #4
        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
        sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

        Comment


        • #5
          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

          Comment


          • #6
            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
            sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X