+ Reply to Thread
Results 1 to 2 of 2

CountA / CountIF across multiple sheets

  1. #1
    Registered User
    Join Date
    03-08-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    14

    CountA / CountIF across multiple sheets

    Hi everyone I am stumped again and am reaching out to the best excel minds I can find.

    I am not able to post my actual workbook because it has confidential data in it so I included something very generic and will explain this as best I can. I know it may I have two sheets I am trying to marry together to create a reservation log that will also count the data I'm pulling from the first workbook. The first sheet contains the name data and is housed on a different computer drive but on the same network. I only want to count cells that have a name in them and I want to exclude the word conversion. So each name should return a value of 1, "conversion" and empty cells should return a value of 0.

    In my first column I have a basic formula to pull the name data over from the first sheet. This simply returns the value of the cell from the first sheet if its name data or blank, the sheet is formatted to not show 0's so empty cells show as empty.

    ='Drive:\Folder1\Folder2\[sheetname.xlsx]tabname'!$F6

    So I tried this formula to get my counts on the sheet I was pulling to

    =COUNTA($F6) but this pulled a value of 1 even if the cell was empty I also tried a =COUNTIF($F6,"<>conversion") and still got a value of 1 for empty cells.

    So then I started working with the premise that I would need to pull the counts from the original sheet that I was pulling the data from and came up with this formula.

    =COUNTA('(drive:\folder1\folder2\[sheetname.xlsx]tabname'!$F12,"<>conversion") This formula however is returning a value of 1 for every cell I also tried with COUNTIF

    Then I tried this one...and it "worked" until I saved and closed my sheet and then when I reopened it later everything came back as ### Value error

    =SUM(COUNTIF('drive:\ Folder 1\Folder2\[sheetname.xlsx]tabname'!$R8,"<>conversion")AND(COUNTA('drive:\Folder1\folder2\[sheetname.xlsx]tabname'!$R8))))

    Excel corrected it to this so I't not sure why it added the * between the first and second arguments -but it was "working" and the correct values were being returned after I closed my sheet and came back everything is ### Value and I am at the end of my knowledge base to get it right.

    =SUM(COUNTIF('drive:\ Folder 1\Folder2\[sheetname.xlsx]tabname'!$R8,"<>conversion")*(AND(COUNTA('drive:\Folder1\folder2\[sheetname.xlsx]tabname'!$R8))))

    So brilliant minds ....please help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: CountA / CountIF across multiple sheets

    I think a lot of your problem is caused by the underlying formula in each cell which is not being interpreted as blank by any of the COUNTA, COUNTIF, COUNTIFS attempts you are making.

    I used SUMPRODUCT successfully.

    On the REs log 1 workbook =SUMPRODUCT(--(LEN(C3:C32)>1)) gives you 9 which is the number of names you have entered.

    If you want to exclude conversion: =SUMPRODUCT(--(LEN(C3:C32)>1))-COUNTIF(C3:C32,"conversion") returns 8.


    Now, a comment on having data stored in separate workbooks. I ALWAYS try to avoid this because you can never be confident of data being pulled from a closed workbook. If I have to use another workbook, I always have them both open.

    Can you find a way to have the data in Res log 2 as a second sheet in REs log 1? It would be much safer and more reliable.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] CountA/CountIF?
    By kailaingrid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-26-2019, 02:50 PM
  2. Counta / Countif
    By mandek in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-31-2016, 12:15 PM
  3. [SOLVED] Countif / Counta
    By joshag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2015, 10:44 AM
  4. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  5. Excel 2007 : CountIF or CountA
    By bjohnsonac in forum Excel General
    Replies: 3
    Last Post: 01-24-2011, 12:46 PM
  6. Excel 2007 : CountA and CountIF
    By DentonHTHS in forum Excel General
    Replies: 3
    Last Post: 04-05-2010, 12:44 AM
  7. countif counta with multiple lookup criteria
    By JR573PUTT in forum Excel General
    Replies: 1
    Last Post: 02-15-2006, 03:37 PM

Tags for this Thread

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