Solved

How to correct a 2010 Excel VALUE error

Posted on 2012-04-11
8
236 Views
Last Modified: 2012-04-11
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
Comment
Question by:bogeybunky
8 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
It should probably be

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

Expert Comment

by:StephenJR
Comment Utility
That said, not sure why you would have got a value error.
0
 
LVL 9

Expert Comment

by:anthonymellorfca
Comment Utility
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

by:bogeybunky
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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

by:bogeybunky
Comment Utility
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

by:
barry houdini earned 500 total points
Comment Utility
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

by:bogeybunky
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now