+ Reply to Thread
Results 1 to 4 of 4

Conditional counting between worksheets

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Conditional counting between worksheets

    Hi,

    I'm a basic excel user and am out of my depth constructing some formulas!

    I have a workbook to record yearly bird sightings. Each month is on a separate sheet, with a summary sheet at the beginning.

    I want to compare data on the same row between sheets, and record if the cell is filled and if the corresponding row in previous sheets is blank; ie if this is the first month we have a record for the bird.

    I have tried

    =IF('Jan 13'!B2="", 'Feb 13'!B2="", 'Mar 13'!B2="", 'Apr 13'!B2="", yes, no)

    =IF('Jan 13'!B2=<0, 'Feb 13'!B2=<0,'Mar 13'!B2=<0, 'Apr 13'!B2=<0,yes, no)

    =AND(B2>0, 'Jan 13'!B2="", 'Feb 13'!B2="", 'Mar 13'!B2="", 'Apr 13'!B2="")


    I then want to count how many positive records were returned; ie total new species seen that month, and copy the data to the summary sheet, but I can do this manually.


    Alternatively, if there is a way or it is easier to do the same thing on the summary sheet; count if the cell is filled and the left hand cells are all blank, down a column, then that would work too.

    ANY help would be much appreciated. I currently do this by hand for 172 rows!

    Attached is a simplified version of my spreadsheet. sample bird records.xlsxsample bird records.xlsx

    Many thanks

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Conditional counting between worksheets

    This automatically pulls the data from the monthly worksheets into a summary sheet using the formula =INDIRECT(ADDRESS(ROW(B2),COLUMN($B2),,,TRIM(B$1) & " 13"))


    sample bird records.xlsx
    Attached Files Attached Files
    Elegant Simplicity............. Not Always

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    232

    Re: Conditional counting between worksheets

    Hi Skidia,

    Here is the code you can put in the Feb 13 tab next to the 1st bird. C2 in your example. You can drag down for that sheet. Copy the code to your ther sheets and change the month listed in the code to match the tab name.

    Please Login or Register  to view this content.
    For the total new species for the year use the following code in B11 in the Feb 13 tab.

    Please Login or Register  to view this content.
    Copy the formula from Andy below to your 11th row on you summaty page drag right
    Please Login or Register  to view this content.
    Any questions please let us know.
    Last edited by nemo74; 04-25-2013 at 02:41 PM.

  4. #4
    Registered User
    Join Date
    04-25-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Conditional counting between worksheets

    Thaks!

    I added an OR function to give a negative result if any of the previous sheets are filled in, giving me this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which seems to work ok.

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1