0

My data consists of a month column A (1 through 12) and a values column B (financial returns) spanning multiple years. How do I count the number of month occurrences in the second half of the year with positive returns?

Let's also say I have values 1 through 12 in a third column C

A formula like this does not seem to work:

=COUNTIFS($B$2:$B$999, ">0", $A$2:$A$999, ">=C6")

C6 simply equals the number 6. I can't seem to pass on boolean/inequality values into the ifs criteria using a cell reference. How do I get around this? I tried removing quotes and using the & but still no luck.

Column B = Returns, Column A = Months (1-12), Column C = 1 through 12 (to present a table of counts).

Thanks for any help! Much appreciated!

New contributor
Zen'z is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 3
    remove the reference from th quotes and concatenate: ">=" & C6 – Scott Craner Dec 3 at 22:04
  • To maintain consistency, you might want to change ">0" to ">"&0. It isn't absolutely necessary but it would make future editing more obvious. – Jeeped Dec 3 at 23:16
0

First let me explain that why you are getting as error using the Formula,,

=COUNTIFS($B$2:$B$999, ">0", $A$2:$A$999, ">=C6")
  • Reason, the command syntax is wrong with second part.

    $A$2:$A$999, ">=C6")

  • Which should written like $A$2:$A$999, ">="&C6),, as also been pointed by few readers through comments above.


Now let me show you the correct formula to use.

  • If you use this:

    =COUNTIFS($H$27:$H$35, ">0", $G$27:$G$35, ">="&J26)
    

or even,,

=COUNTIFS($H$27:$H$35, ">"&0, $G$27:$G$35, ">"&J26)

enter image description here

You get 5 (cell I27), since the formula fails to consider data in Range H27:H35, are >0 (or Positive value).

N.B. Even any Array formula (CSE) fails to do so,, also including this ">"&0.


Now, check the Formula in Cell J27, gets correct count.

=SUMPRODUCT(1*(MONTH(G27:G35)>J$26)*(H27:H35>0))

enter image description here

You may adjust cell references in the formula as needed.

  • a) The OP clearly states 'data consists of a month column A (1 through 12)'. Your COUNTIFS only works because the dates are all greater than 06-Jan-1900 (aka 6). b) You're using ">"&J26 in the COUNTIFS but >J$26 in the SUMPRODUCT. – Jeeped Dec 4 at 10:16
  • @Jeeped,, thanks for observation,,, OP wants to count all Row/Cells have date greater than JUNE ,,, along with +ve values ,,, with shown example dates are genuine and in MM/DD/YY format,,, with Counifs allowed syntax is '">"&J26 but what matters is the operator and is > in both functions! – Rajesh S 2 days ago

Your Answer

Zen'z is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.