Number format

[Vtookup]

Hi all.
i have this formula COUNTIFS(Rec1!$D$4:$R$4,"<>",Rec1!D6:R6,"") to count blanks. now it stop working after i import data by paste value into that range. by paste value, empty space become "0". before i manually input data and leave space empty if zero. how should i amend this formula. thanks in advance for the help.

 

[MARK858]

Assuming you meant count non blanks (which is what your formula does) and not blanks as stated in the post then possibly the below to cover both blanks and 0.

=COUNTIFS('Rec1'!$D$4:$R$4,"<>",'Rec1'!$D$4:$R$4,"<>"&0,'Rec1'!D6:R6,"")

 

[Vtookup]

Thanks MARK858 for the quick reply.
indeed it is non blanks.
but still no result. the space is still empty.
my data came from a formula, and from formula, paste value it by vba. do you think it affected the formula?

 

[MARK858]

I am out for a few hours but will look at it when I get back in but in the meantime can you post the code and also do you have zeroes instead of blanks in both ranges.
You will probably find when I get in that I will post some data to see what results you want.

 

[Vtookup]

Hi again
originally, there are 2 sheets. "stocks" and "Rec1". Input on "Stocks" then transfer to "Rec1".
"Stocks" worksheet counts accumulated data of "Rec1" for max, min, avg and the one i'm having a problem with. believe it or not, it counts blanks.


new sheet "input" have to be added since new data are not categorized. follow the new list while adjacent to it are the old list. just link them make the work easier. then transfer them to "stocks" sheet. instead of 2 steps now it becomes 3. Transferred data (from link) i found out are blanks. so i resorted to pastelink vba. i hope you understand what i mean.

Sub Transfer_Data()
Worksheets("Input").Range("F4:F100").Copy
    Worksheets("Stocks").Range("D4").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False 
End Sub

 

[MARK858]

What is the formula that when copied/pasted as values is producing the 0 from the "empty space"?

 

[MARK858]

Also if you change D4 in the formula below to one of the "empty" cells cell references before you do the copy/paste what does it return?

=CODE(D4)

 

[Vtookup]

It's a simple link. like the old order is ABCDE... and the new order CEADB...
sample A2=A, B2=1; A3=B, B3=0 or blank; A4=C; B4=3, and on the other side
D2=C; E2=(=B4). simple as that. tedious on linking. but as you have that template. it is ease of use.
i can also use index match. but the fact is, the value for transfer is from a formula. hence paste value. or there are other ways...

 

[MARK858]

See posts number 6 and 7, please answer the questions asked rather than elaborate on what hasn't been asked.

 

[Vtookup]

Hi again.
on #6 , No formula, just input numbers, empty or skipped cells becomes zero when linked, formula you asked, is not really a formula but data link to the counterpart in the adjacent column. #8 , Apple: 3, Banana: 10, Cantaloupe: none...
on #7 , D4 is a recipient. no formula just a blank space. which again will turn empty after data transfer to "Rec1" worksheet.
hope you get what i mean.
thanks