Not understanding COUNTIFS workaround in 2003

  • Thread starter Thread starter Raymond W.
  • Start date Start date
R

Raymond W.

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.
 
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
 
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?
 
this seemed to worked for me.

=SUMPRODUCT(--(UPPER(A1:A9)="HIGH")*(UPPER(B1:B9)="TRUE"))
 
The "TRUE/FALSE" is input into the cell by a
checkbox elsewhere.
=SUMPRODUCT(--(A1:A100="high"),--(B1:B100="FALSE"))
Click to expand...

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))
 
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!
 
Back
Top