Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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. ## 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. I agreed to these rules
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. ## 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. I agreed to these rules
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. ## 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

##### Users Browsing this Thread

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

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