Countif

  • Thread starter Thread starter frank716
  • Start date Start date
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
 
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
 
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
 
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
 
Thank you Dan!
For reference the function SUMPRODUCT is called PRODUKTSUMMA
in swedish and it works like a charm.

....johahn
 
Back
Top