Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to correct a 2010 Excel VALUE error

Posted on 2012-04-11
Medium Priority
252 Views
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.
0
Question by:bogeybunky

LVL 24

Expert Comment

ID: 37833280
It should probably be

=COUNTIFS(C1:C4,A1,B1:B4,">"&D1)
0

LVL 24

Expert Comment

ID: 37833360
That said, not sure why you would have got a value error.
0

LVL 9

Expert Comment

ID: 37833496
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.
0

Author Comment

ID: 37833630
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.
0

LVL 24

Expert Comment

ID: 37833934
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)*(B1:B4>E1))
0

Author Comment

ID: 37834080
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.
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 37834319
COUNTIFS will allow multicolumn ranges.....but they all need to be the same size within a single formula. Try this version for your result

=SUMPRODUCT((C1:D4=A1)*(B1:B4>E1))

regards, barry
0

Author Closing Comment

ID: 37834382
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll