using the COUNTIF function

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am having difficulty in writing a formula to acheive a
certain result. It is:

I wish to count the number of occurrences of specific
text or numbers in each of two columns of a number a
rows. To be specific, I have a large amount of statistics
in a speadsheet, and wish to identify, in how many
rows, "1" appears in column E, and "S" in column F, and
in the same row.

I have been using COUNTIF and AND, but it does not quite
work. The actual formula being "=COUNTIF(($E$5:$F$5):
($E300:$F300),AND("1","S"))". It's a valid formula, but
does not work!

Of course the "$"'s can be ignored as they are only
included for other reasons.

Any ideas?
 
I'm not sure why you think the formula is valid.

AND("1","S")

having no boolean values, returns the #VALUE! error. Granted,
COUNTIF() doesn't pass the error through...

Try:

=SUMPRODUCT(--($E$5:$E$300=1),--($F$5:$F$300="S"))
 
Correction - it *IS* a valid formula if you're trying to count the
#VALUE! errors in the range.
 
Thank you Aladin, but it dod not work. The formula was
accepted, but it did not count the number of occurrences
I was looking for. However, I had not though to use
SUMPRODUCT, and will experiment withit further.
 
Thank you - for both messages.

When I said that the formula was valid, I meant that
Excel accepts it as a formula, but it does not do what I
want it to!

I have tried your suggestion, both with and without the "-
-", which I do not understand. Regretfully, it does not
work either.

I still feel that COUNTIF should do it. It works fine
when only looking for occurrances in one column. The
problem is getting it to identify them in two columns,
and both in the same row.
 
Both Aladin and JE's formulas are equivalent. Both should work.

If your 1 is stored as a string, then you would need to test for that in the
formulas (rather then the numeral 1 which they used)

=SUMPRODUCT(($E$5:$E$300="1")*($F$5:$F$300="S"))

=SUMPRODUCT(--($E$5:$E$300="1"),--($F$5:$F$300="S"))


or if you don't care whether it is stored as a string or a number

=SUMPRODUCT(--(--$E$5:$E$300=1),--($F$5:$F$300="S"))
 
What exactly do you mean by "does not work? Wrong value? Error?

Perhaps I'm misunderstanding your setup, but COUNTIF will assuredly
*NOT* work if this is your situation. Regardless of your feeling,
COUNTIF doesn't compare arrays, therefore can't take into account
whether the values are in the same row:

E F

5 1 T
6 2 S
7 1 S
8 ...

OTOH,

=SUMPRODUCT(--($E$5:$E$300=1),--($F$5:$F$300="S"))

returns 1 in that situation

I just noticed that you used "1" instead of 1, so if your data in
column E is Text, change the above to

=SUMPRODUCT(--($E$5:$E$300="1"),--($F$5:$F$300="S"))
 
Back
Top