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