0

I'm saerching for a formula comparing two columns f and g with h and i taking a tolerance into account. I have coordinates f and g and coordinates h and i and I want to know how often f equals or is simlar to h and g to i here with a tolerance of 2. But both criteria Need to be met for the Count.

I've tried things like this without succes: =SUMPRODUCT((F:F=H:H)(G:G=I:I)(F:I>0))/4

=SUMMPRODUCT(((ABS(F:F)-(H:H)<=2))(ABS((G:G)-(I:I)<=2))(F:I>0))

COUNTIF didn't work at all.

Improve this question
4
  • Avoid using full column references with the SUMPRODUCT function. Blank cells are calculated, not overlooked like other functions and SUMPRODUCT cannot happily skip over text when performing maths like SUMIFS can. If you have text labels in F1, G1, etc, you are probably receiving a #VALUE! error. Do you have some sample data together with expected results that you can add to your question? Commented Apr 4, 2015 at 11:57
  • Yes you are right, if text occurs I'm receiving a mistake, thats why I also needed to add >0. I have a sample but don't have enough Reputation to post it. Is there another possibility? Commented Apr 4, 2015 at 12:22
  • If you just edit your post (edit right under the excel | excel-formula tags) you should be able to paste some sample data and expected results into your question. Don't worry too much about formatting. Alternately, put it on GoogleDrive or DropBox and post a link back here. Commented Apr 4, 2015 at 13:02
  • dropbox.com/s/fqskpti1ly9rtdb/sample.jpg?dl=0 Commented Apr 4, 2015 at 16:37

1 Answer 1

Reset to default
1

With some random data I've made up this is working: assume F1:I6 are your coordinate pairs. 

={SUM(IF((ABS(F1:F6-H1:H6)<=4)*(ABS(G1:G6-I1:I6)<=4);1;0))}

Enter as a matrix formula (Ctrl-Shift-Enter). The 4 stands for a tolerance of +- 2, adjust for your needs.
If you really have to cope with cells containing text within that range you need to test the cell first before subtracting: 

{=SUM(IF((ABS(IF(ISTEXT(F1:F6);-9999;F1:F6)-IF(ISTEXT(H1:H6);-8888;H1:H6))<=4)*(ABS(IF(ISTEXT(G1:G6);-9999;G1:G6)-IF(ISTEXT(I1:I6);-8888;I1:I6))<=4);1;0))}

This looks ugly but it works. The formula substitutes a cell with text with a token value of -9999 or -8888. These values should never occur in the real data. I used 2 distinct values to cover the case where only 1 or both columns contain text. The difference of the values needs to be greater than the tolerance.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.