COUNTIF Function

[Kate]

Hello,

I have a spreadsheet with 2 rows that look something like
this:

6/1 YES
6/1 YES
6/1 NO
6/2 NO
6/2 YES
6/2 YES

I need to create a formula that will count all of the
yes's on 6/1, and all of the yes's on 6/2,etc, so that it
would use this logic:

If range A1:A6=6/1, then (the result of the formula is)#of
yes's in range B1:B6.

Can you help?

Thank you.

Kate

 

[Frank Kabel]

Hi Kate
try
=SUMPRODUCT(--(A1:A10="6/1"),--(B1:B10="Yes"))

assuming '6/1' is a text entry and not a date value

 

[Soo Cheon Jheong]

Hi,

----A-----B-----C------D------E------F-------
1 6/1 YES 6/1 YES (Formula)
2 6/1 YES 6/1 NO
3 6/1 NO 6/2 YES
4 6/2 NO 6/2 NO
5 6/2 YES
6 6/2 YES
---------------------------------------------

Enter a formula in F1:

=SUMPRODUCT(--($A$1:$A$6=D1),--($B$1:$B$6=E1))

then drag and fill down.


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Howard]

Hi, sorry for lurking but have a similar question. I have a set of grades
A-E for individuals in different classes

i.e.
Class name grade
abc fred A
abc jim B
def sally B
def alice E

I'd like a sort of sumtotal under each change in class that tells me
(ultimately) the percentage of grades in the range A to C for that class. I
don't mind if it only counts them at this stage as I can find the pecentage
from that.

Is this a similar formula?
BTW what do the -- signs do in the sumproduct formula?

Howard

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(CODE(C2:C5)<=CODE("C")))/COUNTA(C2:C5)

formatted as a percentage.

SUMPRODUCT requires numeric arrays. A comparison (e.g. A<B) returns a
Boolean value (TRUE/FALSE), which SUMPRODUCT ignores. The first of the
double unary minuses coerces TRUE/FALSE to -1/0, respectively. The
second negates the result of the coercion, i.e. to 1/0.

 

[Howard]

Wow!
Thank you.
(..and I thought I knew Excel pretty well. Amazing what you can achieve with
a bit of function fiddling!)
Howard.

 

[Harlan Grove]

One way:

=SUMPRODUCT(--(CODE(C2:C5)<=CODE("C")))/COUNTA(C2:C5)

...

Another,

=COUNTIF(C2:C5,"<=C*")/COUNTA(C2:C5)