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!

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!

## Comment