Countif

F

frank716

I have 2 columns A & B with the following values:

A B
1 4
1
2 5
2
3 6
3

How can I count the number of cells that A=3 with B="" using excel
built-in
functions?
How can I combine the 2 criteria?

Thanks A Lot!!

Frank
 
D

Dan E

Frank,

Bob's formula works as follows
=sumproduct((A1:A10=3)*(B1:B10=""))

Unless A1:A10 = 3 it puts in a FALSE (ie 0)
If A1:A10 = 3 it puts in a TRUE (ie 1)
Unless B1:B10 = "" it puts in a FALSE (ie 0)
If B1:B10 = "" it puts in a TRUE (ie 1)

You get two arrays produced of TRUES and FALSES
for your example
A B
1 4
1
2 5
2
3 6
3

Array 1 (F, F, F, F, T, T)
Array 2 (F, T, F, T, F, T)

It then multiplies the two (treating T's as 1 and F as 0)

Produced array (0, 0, 0, 0, 0, 1)

It then sums the produced array

Dan E
 
J

Johan Hahn

Hi!
I have a similar problem to "frank716".

I have 20 values (B4:B23 and I have to answer the question:
"How many of those values lies between the values of B24 and B25?"

Whats more annoying is that I have the swedish version of excel 2002
(10.2614.3311) so here is a crash-course in swedish:

ANTAL = COUNT
OM = IF
OCH = AND

Based on the answer to franks716's question I wrote...
=ANTAL((B4:B23>B24)*(B4:B23<B25))
....but that gives 0 as a result (which is wrong).

After reading http://www.microsoft.com/office/using/column10.asp
I also tried another approach...
=ANTAL.OM(B4:B23; OCH(">"&B24;"<"&B25) )
.... but that also resulted in 0.

I've pretty much ran out of ideas, so I'd appreciate your help!
....johahn
 
D

Dan E

Johan,

The solution given used the SUMPRODUCT function, not the COUNT
function. Try to find the SUMPRODUCT function. Since I don't speak
swedish I have no clue what the translation is or might be???

Not sure this will help but here is the description of the SUMPRODUCT
function:
Multiplies corresponding components in the given arrays, and returns the
sum of those products.

Use the exact formula you showed
=ANTAL((B4:B23>B24)*(B4:B23<B25))

But replace ANTAL with the Swedish SUMPRODUCT.

Dan E
 
J

Johan Hahn

Thank you Dan!
For reference the function SUMPRODUCT is called PRODUKTSUMMA
in swedish and it works like a charm.

....johahn
 
Top