Using COUNTIF to count with multiple logical requirements.

[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

 

[Domenic]

Try,

=SUM(IF((A1:A8=3)*(B1:B8="y"),1,0)), entered using Ctrl+Shift+Enter

Cheers!

 

[Jason Morin]

COUNTIF won't help you in this case. Try something like:

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

HTH
Jason
Atlanta, GA

 

[Ken Wright]

=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.

 

[sgore]

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

 

[sgore]

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..

 

[Ken Wright]

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.

 

[Peo Sjoblom]

You can use A1:A65535
surely there is no need to check A65536?

 

[Harlan Grove]

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.

...

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.

 

[JE McGimpsey]

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.

 

[Ken Wright]

LOL - You know it

 

[Ken Wright]

LOL, Hi Harlan - methinks you're in a picky mood today ( as I'm pretty sure you
know what I meant <g> )

 

[Harlan Grove]

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.

...

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: '.

 

[Ken Wright]

LOL - Serves 'me' right for being picky now, as I hadn't considered that aspect
;-)