Countif and Right functions

  • Thread starter Thread starter Jesse
  • Start date Start date
J

Jesse

Example

Column A Column B
ONDJFMAMJJAS 4
ONDJFMAMJ
ONDJFMAMJJAS 5
ONDJFMAMJJAS
ONDJFMA JJ 3
ONDJFMAMJJAS
ONDJFMAMJJAS 3
ONDJFMAMJJAS 6
FMAMJJAS
OJFMAMJJ 1


I have 2 conditions that I'd like to count the occurrences in. 1) the right most text in column A must be "S" and the value in Column B must be >= 1.

I tried the COUNTIFS function (=COUNTIFS(A9:A55,"*S*",B9:B55,">=1") which kind of works but only accounts for specific text and I need the rightmost text.

I think I need the RIGHT function but can't seem to get it to work.

Little help?

thanks!

Jesse
 
Hi Jesse,

Am Mon, 6 Oct 2014 12:08:14 -0700 (PDT) schrieb Jesse:
Example

Column A Column B
ONDJFMAMJJAS 4
ONDJFMAMJ
ONDJFMAMJJAS 5
ONDJFMAMJJAS
ONDJFMA JJ 3
ONDJFMAMJJAS
ONDJFMAMJJAS 3
ONDJFMAMJJAS 6
FMAMJJAS
OJFMAMJJ 1
Click to expand...

try it with:
=SUMPRODUCT(--(RIGHT(A9:A55,1)="S"),--(B9:B55>=1))


Regards
Claus B.
 
Hi,

Am Mon, 6 Oct 2014 12:08:14 -0700 (PDT) schrieb Jesse:
I tried the COUNTIFS function (=COUNTIFS(A9:A55,"*S*",B9:B55,">=1") which kind of works but only accounts for specific text and I need the rightmost text.
Click to expand...

or try it with
=COUNTIFS(A9:A55,"*S",B9:B55,">=1")

Put only an asterix in front of the S


Regards
Claus B.
 
The COUNTIFS function works well. I tried the sumproduct as well (never knew about the "--" gives a numeric) and added one more thing to not count the blanks. Not sure why the ">=1" counts blanks but adding the specific seems to work well. Now I have two methods. THANKS AGAIN!!!

=SUMPRODUCT(--(RIGHT(F9:F55,1)="S"),--(G9:G55>=1),--(G9:G55<>""))
 
Jesse said:
The COUNTIFS function works well.
Click to expand...

It is also the more efficient implementation.

However, COUNTIFS cannot be used if you use an Excel file name ending in
".xls". (Excel 2003 compatibility mode in Excel 2007 and later.)


Jesse said:
I tried the sumproduct as well [...] and added one more
thing to not count the blanks. Not sure why the ">=1"
counts blanks
=SUMPRODUCT(--(RIGHT(F9:F55,1)="S"),--(G9:G55>=1),--(G9:G55<>""))
Click to expand...

There is a difference between an __empty__ cell (contains no constant and no
formula) and a cell __value__ that __looks__ blank because it is the null
string, which is typically written "" (two adjacent double-quotes).

The latter is text. And in a comparison like G9:G55>=1, text is always
considered "greater than" a number -- even numeric text. So "0">=1 is true,
whereas 0>=1 is false. But more to the point: "">=1 is true.

Caveat: A cell can look empty, but its value is actually the null string.
This can happen when we copy a cell value that is the null string,
paste-special-value into a cell. The only way to be sure a cell is empty is
to use the function ISBLANK(A1). Ironically, the function name is a
misnomer. Read it as "ISEMPTY".


Jesse said:
(never knew about the "--" gives a numeric)
Click to expand...

The double-negate operation is not sacrosanct. Any arithmetic operation(s)
that have the same effect will do. For example, multiply by 1 or add zero.
(I prefer "--" myself.)

So Claus's original formula could be written more succinctly as:

=SUMPRODUCT((RIGHT(F9:F55,1)="S")*(G9:G55>=1))

(Aside.... There is an endless debate about which form (if either) is more
efficient. And there are contexts where either form is preferred or
required for various reasons.)

The point is: numeric text is interpreted as a number when it is used in an
arithmetic expression, but not in a comparison. And most functions that
accept numeric parameters also interpret numeric text as a number.

(Notable exceptions: lookup functions like MATCH, LOOKUP and VLOOKUP.)

For example, ="123"*10 results in the number 1230. MOD("123",10) results in
the number 3.

This is useful when using string functions (LEFT, RIGHT, MID, SUBSTITUTE) to
manipulate numeric text.
 
There is a difference between an __empty__ cell (contains no constant
and no formula) and a cell __value__ that __looks__ blank because it
is the null string, which is typically written "" (two adjacent
double-quotes).

The latter is text. And in a comparison like G9:G55>=1, text is
always considered "greater than" a number -- even numeric text. So
"0">=1 is true, whereas 0>=1 is false. But more to the point: "">=1
is true.

Caveat: A cell can look empty, but its value is actually the null
string. This can happen when we copy a cell value that is the null
string, paste-special-value into a cell. The only way to be sure a
cell is empty is to use the function ISBLANK(A1). Ironically, the
function name is a misnomer. Read it as "ISEMPTY".
Click to expand...

I use the LEN() function to test because it returns zero if it contains
the null string as a result of a formula...

=IF(AND(LEN(A1),A1>0),B1/A1,0)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
[joeu2004 wrote:]
Caveat: A cell can look empty, but its value is actually the null
string. This can happen when we copy a cell value that is the null
string, paste-special-value into a cell. The only way to be sure a cell
is empty is to use the function ISBLANK(A1). Ironically, the function
name is a misnomer. Read it as "ISEMPTY".
Click to expand...

I use the LEN() function to test because it returns zero if it
contains the null string as a result of a formula...
=IF(AND(LEN(A1),A1>0),B1/A1,0)
Click to expand...

Right: you are describing what to use if you do __not__ want to distinguish
a truly empty cell from a null string. (I prefer A1<>"". But it doesn't
matter.)

On the contrary, I was describing what to use if you __do__ want to
distinguish the two ("be sure a cell is empty"), notably a null string
resulting from copy-and-paste-special-value.

But the key take-away is: we cannot just rely on visual inspection ("a cell
can look empty, but its value is actually the null string").
 
GS said:
[joeu2004 wrote:]
Caveat: A cell can look empty, but its value is actually the null
string. This can happen when we copy a cell value that is the null
string, paste-special-value into a cell. The only way to be sure
a cell is empty is to use the function ISBLANK(A1). Ironically,
the function name is a misnomer. Read it as "ISEMPTY".
Click to expand...

I use the LEN() function to test because it returns zero if it
contains the null string as a result of a formula...
=IF(AND(LEN(A1),A1>0),B1/A1,0)
Click to expand...

Right: you are describing what to use if you do __not__ want to
distinguish a truly empty cell from a null string. (I prefer A1<>"".
But it doesn't matter.)

On the contrary, I was describing what to use if you __do__ want to
distinguish the two ("be sure a cell is empty"), notably a null
string resulting from copy-and-paste-special-value.

But the key take-away is: we cannot just rely on visual inspection
("a cell can look empty, but its value is actually the null string").
Click to expand...

Agreed! It's a crap shoot either way IMO, but hopefully the user enters
values of the correct type or at least - formats correctly!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top