bogeybunky
asked on
How to correct a 2010 Excel VALUE error
I am new to Excel so this is probably a very simple solution, but I cannot figure it out and any help would be greatly appreciated.
Column A Column B Column C Column D
Row 1 Divergence 25 Divergence 10
Row 2 -25 Divergence
Row 3 25 sssssss
Row 4 25 Divergence
I created the following forumla: =COUNTIFS(C1:C4,A1,B1:B4," >D1")
It returns the VALUE error. I think I understand that there are 2 different data types here that is causing the problem, but I cannot figure out how to fix it.
What I am trying to do is find the number of times the word divergence is found with positive value in column B. The answer is 2.
Thank you.
Column A Column B Column C Column D
Row 1 Divergence 25 Divergence 10
Row 2 -25 Divergence
Row 3 25 sssssss
Row 4 25 Divergence
I created the following forumla: =COUNTIFS(C1:C4,A1,B1:B4,"
It returns the VALUE error. I think I understand that there are 2 different data types here that is causing the problem, but I cannot figure out how to fix it.
What I am trying to do is find the number of times the word divergence is found with positive value in column B. The answer is 2.
Thank you.
That said, not sure why you would have got a value error.
I can't generate the error either - and probably above is definitely having tried it I get answer 2, with the addition of value 10 in D4 which I presume was a question typo.
ASKER
Okay, making some progress here. I think I over simplified. The following is more like my real problem
Column A Column B Column C Column D Column E
Row 1 Divergence 25 Divergence Divergence 10
Row 2 -25 Divergence Divergence
Row 3 25 sssssss ssssss
Row 4 25 Divergence Divergence
Formula: =COUNTIFS(C1:D4,A1,B1:B4," >"&D1)
I get the correct answer with only Column C, but when I add Column D I am back to VALUE error.
By the way either ">"&D1 or ">"D1 works with only Column C, but both give the error when I add Column D.
Column A Column B Column C Column D Column E
Row 1 Divergence 25 Divergence Divergence 10
Row 2 -25 Divergence Divergence
Row 3 25 sssssss ssssss
Row 4 25 Divergence Divergence
Formula: =COUNTIFS(C1:D4,A1,B1:B4,"
I get the correct answer with only Column C, but when I add Column D I am back to VALUE error.
By the way either ">"&D1 or ">"D1 works with only Column C, but both give the error when I add Column D.
That's a different set up. You can only have a single colum reference so C1:D4 won't work.
=SUMPRODUCT((C1:C4=A1)*(D1 :D4=A1)*(B 1:B4>E1))
=SUMPRODUCT((C1:C4=A1)*(D1
ASKER
Thank you very much StephenJR.
That does indeed work to get rid of the VALUE error, but the answer is not what I am looking for. The formula you gave me produces 2 as the result when the answer I am looking for is 4.
I want to know the number of times the word "Divergence" appears when the corresponding value in column B is greater than 10. That condition occurs twice in column C and twice in Column D. So I am sure there is some subtlety that I am missing.
That does indeed work to get rid of the VALUE error, but the answer is not what I am looking for. The formula you gave me produces 2 as the result when the answer I am looking for is 4.
I want to know the number of times the word "Divergence" appears when the corresponding value in column B is greater than 10. That condition occurs twice in column C and twice in Column D. So I am sure there is some subtlety that I am missing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much to all who helped. I have been a Quattro Pro user for many, many years, but because of instability problems decided I had to learn Excel. Your help has been invaluable to me. thanks again. I am sure I will be back.
=COUNTIFS(C1:C4,A1,B1:B4,"