Positive & Negative Columns

[codeman_not]

Hoping someone here can help me. Essentially I have 4 columns. Each column will have either a positive or negative value. In the 5th column I am trying to tally up how many of those 4 columns are positive or negative. I tried using the COUNTIF function and it works for all scenarios except when they're are 3 positives or 3 negatives (in which case, the value is +2 or -2, respectively). Obviously, this makes sense since if you do the math (1+1+1-1 = 2), but what I am trying to show in that 5th column is that there is 3 out of 4 columns that are positive (looking for +3 in that case). Does anyone have any other ideas that might work? Much appreciated!

 

[diddi]

hi and welcome to the board. could you clarify for me the values in your columns. can they be any positive or negative number or only +1 or -1

 

[Rick Rothstein]

I wish you had posted your COUNTIF formula because, assuming I understand your setup correctly, it should have worked (your description sounded more like you were using SUMIF though). Anyway, assuming your four columns are A:D, and assuming your first line of data is Row 1, put this formula in E1 for counting positive values...

=COUNTIF(A1:D1,">0")

and this formula for negative values...

=COUNTIF(A1:D1,"<0")

Note that neither one of these formulas count zeroes. If you want that counted with, say, the postive values, then change the second argument from ">0" to ">=0".

 

[mikerickson]

How about
=COUNTIF(A1:D1,">0")>=3

 

[codeman_not]

Sorry all (and thanks for the quick replys) - I should have provided more details. Anyways, to answer diggi's questions - yes, the preceding 4 columns will have either a "1" or a "-1" in them. Rick, you are correct, I was using SUMIF (not COUNTIF). As I mentioned, that works in all instances except when there are (1,1,1,-1) or (-1,-1,-1,1) or any sequential variation of them. Thanks again all.

 

[codeman_not]

Wow, my bad... was using SUM (not SUMIF).