Forum Discussion
What is wrong with this formula?
excelisfunny wrote: ``I suppose when you start a question off with "why doesn't this work" without explaining what I'm trying to do...``
Yup. GIGO!
-----
excelisfunny wrote: ``I need the total count of [...] those have to be over 1 to count, PLUS the total count of four other cells that have to be over 0 [....] If there is another way to do it I'm all ears.``
For that purpose, your COUNTIF(...)+COUNTIF(...) expression is exactly how I would do it.
But I would eschew the outer parentheses. IOW, I would write =COUNTIF(...)+COUNTIF(...).
I never said nor implied otherwise. As you noted, I could not comment on it since I did not know your intent.
AA2:AB2 2 columns.
AC2:AF2 4 columns.
I'm confused on why that matters... Detlef_Lewin
A workaround that I used that ended up working was:
=(COUNTIF(AB2:AC2,">1") + COUNTIF(AD2:AH2,">0"))
I'm confused on why that matters...Because Microsoft says so:
The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.
Detlef_Lewin wrote: ``Different count of columns.``
excelisfunny wrote: ``I'm confused on why that matters``
It "matters" because the COUNTIFS help page states: ``Each additional range must have the same number of rows and columns as the criteria_range1 argument.``
Simple as that.
-----
excelisfunny wrote: ``A workaround that I used that ended up working was:
=(COUNTIF(AB2:AC2,">1") + COUNTIF(AD2:AH2,">0"))``
But that does not have the same meaning. Which probably explains why you do not understand the aforementioned requirement (same number of columns and rows).
COUNTIFS(AA2:AB2,">1",AC2:AF2,">0") counts 1 where each cell in the first range (AA2:AB2) exceeds 1 and the corresponding cell in the second range (AC2:AF2) exceeds zero.
With your ranges, AC2 corresponds to AA2 and AD2 corresponds to AB2.
But nothing in the first range corresponds to AD2 and AF2. Hence the #VALUE error.
Your alternative simply counts all cells in the first range that exceed 1 plus all the cells in the second range that exceed 0.
If AA2>1 but AC2<=0, you will count 1. If AA2>1 and AC2>0, you will count 2.
In contrast, COUNTIFS would count 0 in the first case, and it would count only 1 in the second case.
- I suppose when you start a question off with "why doesn't this work" without explaining what I'm trying to do...you can only answer with why the equation doesn't work, not what will help me solve my need. I'll have to get better at asking questions on tech threads.
I have 6 cells columns in a row that I'm tracking the count of. I need the total count of two of the cells, and those have to be over 1 to count, PLUS the total count of four other cells that have to be over 0. So they won't be the same number of columns. I had assumed COUNTIFS was the equation I could use but it turns out I was wrong and that isn't the appropriate use of that equation. However, adding two different COUNTIF equations did the trick, as mentioned in my workaround. If there is another way to do it I'm all ears.
