Using COUNTIF to count with multiple logical requirements.

  • Thread starter Thread starter sgore
  • Start date Start date
S

sgore

Example:

C1 C2
1 y
1 y
2 n
2 y
2 y
3 y
3 y
3 n

What I need is to count the number of instances for (C1) that have
value of 3 and the value of y in (C2)

Results: Count = 2 as there are two occurances of (Column 1) = 3 an
(Column 2) = y.

I tried to maniuplate the COUNTIF statement to do this but m
in-experience with EXCEL has got the best of me.

I have attached a test spreadsheet to give you a visual of what I a
trying to do. I am sure this is not hard, I am just not aware of ho
to manipulate each function the way I want
 
COUNTIF won't help you in this case. Try something like:

=SUMPRODUCT((A1:A10=3)*(B1:B10="y"))

HTH
Jason
Atlanta, GA
 
=SUMPRODUCT((RngA=3)*(RngB="y")) will give you a COUNT of this.

RngA = whatever range holds your 1,2,3s and RngB is the y/ns - Must be the same
size ranges.
 
Jason,

That works for a small data range. I tried to modify it to look at al
of the column for this value

Example

=SUMPRODUCT((A:A=3)*(B:B="y"))

I then get an error when I do this.

ERROR: #NUM!

I am not sure why I am getting this error.

I have also tried this and get the same error.

=SUM(IF((A1:A8=3)*(B1:B8="y"),1,0)), entered using Ctrl+Shift+Ente
 
I have found in a few post that the SUMPRODUCT function does not allo
you to use the entire column.

Bummer.

Anyone have any other ideas I can try? I really need to be able to d
this on an entire column basis.

Thanks for all the help..
 
Guess what - Your two options of SUMPRODUCT or arrays will not work on full
columns. If you really need a range that big, then either use A1:A65535 etc, or
use a dynamic range created through use of the OFFSET function.
 
Guess what - Your two options of SUMPRODUCT or arrays will not work on full
columns. If you really need a range that big, then either use A1:A65535 etc,
or use a dynamic range created through use of the OFFSET function.
Click to expand...
...

Neither SUMPRODUCT not arrays will work with full column range references
produced by OFFSET. There's just no way to work with full column ranges in many
(most?) single funtion calls.
 
I'd bet that Ken meant to use a dynamic range, which would likely have
significantly fewer cells than the hardcoded A1:A65536, rather than
using it as a substitute for an entire column.
 
LOL, Hi Harlan - methinks you're in a picky mood today ( as I'm pretty sure you
know what I meant <g> )
 
I'd bet that Ken meant to use a dynamic range, which would likely have
significantly fewer cells than the hardcoded A1:A65536, rather than
using it as a substitute for an entire column.
Click to expand...
...

I'm pretty sure you're right about what Ken meant. However, it was possible to
interpret his reply to mean that OFFSET could be used to produce a reference to
entire column ranges that could be used. Guess I should have been more explicit
and preceded my comments with 'Picky: '.
 
Back
Top