# Excel “IFS” criteria values using cell references

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.
• 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

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)
``````

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))
``````

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