I have been reading up on how to work around the lack of a COUNTIFS function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows that
contain both "High" and "False". I either get #VALUE! errors, or counts of 0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or false,
but that is the wording at my disposal...I feel as if I am beating my head
against an invisible wall. If I cannot make it see TRUE or FALSE I suppose I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

Sumproduct is the 2003 alternative to Countifs. Something like:

=sumproduct(--(a1:a100="high"),--(b1:b100))

Regards,
Fred.

"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows that
contain both "High" and "False". I either get #VALUE! errors, or counts of
0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or false,
but that is the wording at my disposal...I feel as if I am beating my head
against an invisible wall. If I cannot make it see TRUE or FALSE I suppose
I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

I have tried this. My result was always an answer of zero. Here is what I
used exactly:

=SUMPRODUCT(--(A1:A100="high"),--(B1:B100="FALSE"))


No matter how many rows I type in "high" on column A and then "FALSE" on
column B, the number never climbs. (A1 = high, and B1 = FALSE, result is
still 0). I even formatted the cell as text to make sure it wasn't
conflicting with an internal true/false operator. I tried in all capitals,
lowercase....

It works with anything other than true/false however

"Fred Smith" wrote:

Sumproduct is the 2003 alternative to Countifs. Something like:

=sumproduct(--(a1:a100="high"),--(b1:b100))

Regards,
Fred.

"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows that
contain both "High" and "False". I either get #VALUE! errors, or counts of
0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or false,
but that is the wording at my disposal...I feel as if I am beating my head
against an invisible wall. If I cannot make it see TRUE or FALSE I suppose
I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

Ah... I think I figured out the issue. The "TRUE/FALSE" is input into the
cell by a checkbox elsewhere. If I type "false" it works. if I use the
checkbox it does not. Is there a way around this?

"Fred Smith" wrote:

Sumproduct is the 2003 alternative to Countifs. Something like:

=sumproduct(--(a1:a100="high"),--(b1:b100))

Regards,
Fred.

"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows that
contain both "High" and "False". I either get #VALUE! errors, or counts of
0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or false,
but that is the wording at my disposal...I feel as if I am beating my head
against an invisible wall. If I cannot make it see TRUE or FALSE I suppose
I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

this seemed to worked for me.

=SUMPRODUCT(--(UPPER(A1:A9)="HIGH")*(UPPER(B1:B9)="TRUE"))

--


Gary Keramidas
Excel 2003


"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows that
contain both "High" and "False". I either get #VALUE! errors, or counts of 0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or false,
but that is the wording at my disposal...I feel as if I am beating my head
against an invisible wall. If I cannot make it see TRUE or FALSE I suppose I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

The "TRUE/FALSE" is input into the cell by a
checkbox elsewhere.
=SUMPRODUCT(--(A1:A100="high"),--(B1:B100="FALSE"))

If the cells are linked cells for the checkboxes then they return logical
TRUE/FALSE which are not text entries. Try removing the quotes from around
FALSE.

=SUMPRODUCT(--(A1:A100="high"),--(B1:B100=FALSE))

--
Biff
Microsoft Excel MVP


"Raymond W." wrote in message
...
Ah... I think I figured out the issue. The "TRUE/FALSE" is input into the
cell by a checkbox elsewhere. If I type "false" it works. if I use the
checkbox it does not. Is there a way around this?

"Fred Smith" wrote:

Sumproduct is the 2003 alternative to Countifs. Something like:

=sumproduct(--(a1:a100="high"),--(b1:b100))

Regards,
Fred.

"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows
that
contain both "High" and "False". I either get #VALUE! errors, or counts
of
0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If
VBA
is the only way to accomplish this, I am fine using that, just need
some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or
false,
but that is the wording at my disposal...I feel as if I am beating my
head
against an invisible wall. If I cannot make it see TRUE or FALSE I
suppose
I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

3 comments:
You don't need UPPER, as the comparison is not case sensitive.
You don't need the double unary minus if you are using the * operator to
multiply.
As others have suggested, in your formula you need to be careful to
distinguish between a text "TRUE" and a logical value TRUE. The quotes make
it text.
--
David Biddulph

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
this seemed to worked for me.

=SUMPRODUCT(--(UPPER(A1:A9)="HIGH")*(UPPER(B1:B9)="TRUE"))

--


Gary Keramidas
Excel 2003


"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows
that
contain both "High" and "False". I either get #VALUE! errors, or counts
of 0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If
VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or
false,
but that is the wording at my disposal...I feel as if I am beating my
head
against an invisible wall. If I cannot make it see TRUE or FALSE I
suppose I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

Thank you Biff, and Thank you David. Your solutions worked perfectly for me.
I didn't think about the return being a logical value. True and false are
treated as logical values until after you format the cell as text and then
retype true or false into the box. This was my error. Removing the quotes
solves the issue.

Thanks guys!

"David Biddulph" wrote:

3 comments:
You don't need UPPER, as the comparison is not case sensitive.
You don't need the double unary minus if you are using the * operator to
multiply.
As others have suggested, in your formula you need to be careful to
distinguish between a text "TRUE" and a logical value TRUE. The quotes make
it text.
--
David Biddulph

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
this seemed to worked for me.

=SUMPRODUCT(--(UPPER(A1:A9)="HIGH")*(UPPER(B1:B9)="TRUE"))

--


Gary Keramidas
Excel 2003


"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and a
column with two (True, False). I am wanting a total count of all rows
that
contain both "High" and "False". I either get #VALUE! errors, or counts
of 0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If
VBA
is the only way to accomplish this, I am fine using that, just need some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or
false,
but that is the wording at my disposal...I feel as if I am beating my
head
against an invisible wall. If I cannot make it see TRUE or FALSE I
suppose I
can use an IF statement elsewhere to change it to a 1 or 0 and use that
column instead... but that just seems like extra bloat.

Thanks in advance for the help

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Raymond W." wrote in message
...
Thank you Biff, and Thank you David. Your solutions worked perfectly for
me.
I didn't think about the return being a logical value. True and false are
treated as logical values until after you format the cell as text and then
retype true or false into the box. This was my error. Removing the quotes
solves the issue.

Thanks guys!

"David Biddulph" wrote:

3 comments:
You don't need UPPER, as the comparison is not case sensitive.
You don't need the double unary minus if you are using the * operator to
multiply.
As others have suggested, in your formula you need to be careful to
distinguish between a text "TRUE" and a logical value TRUE. The quotes
make
it text.
--
David Biddulph

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
this seemed to worked for me.

=SUMPRODUCT(--(UPPER(A1:A9)="HIGH")*(UPPER(B1:B9)="TRUE"))

--


Gary Keramidas
Excel 2003


"Raymond W." wrote in message
...
I have been reading up on how to work around the lack of a COUNTIFS
function
in 2003, but am apparently not doing something correctly.

I have a column with three possible answers (high, low, moderate) and
a
column with two (True, False). I am wanting a total count of all rows
that
contain both "High" and "False". I either get #VALUE! errors, or
counts
of 0
regardless of the data entered.

I am testing the code on '07, but will be running the sheet on '03. If
VBA
is the only way to accomplish this, I am fine using that, just need
some
pointers in the right direction.

Oddly enough it seems to work if I use something other than true or
false,
but that is the wording at my disposal...I feel as if I am beating my
head
against an invisible wall. If I cannot make it see TRUE or FALSE I
suppose I
can use an IF statement elsewhere to change it to a 1 or 0 and use
that
column instead... but that just seems like extra bloat.

Thanks in advance for the help