countif where multiple criteria are met

  • Thread starter Thread starter Kristina
  • Start date Start date
K

Kristina

I want to count all cells in the range be5:be999 where the
value "1" is entered, IF and only IF other criteria are
met. These additional criteria are bn5:bn999 where value
is "H" AND h5:h999 where value is "Y". I have tried
several variations along the lines of
=COUNTIF(AND(BE5:BE999,"1",(BN5:BN999,"H",(H5:H999,"Y"))))

or

=COUNT(AND(COUNTIF(BE5:BE999,"1",COUNTIF
(BN5:BN999,"H",COUNTIF(H5:H999,"Y")))))

But nothing is returning a correct answer.

I would appreciate any help that you could provide.

Thanks,

Kristina
 
Kristina,

=SUMPRODUCT((BE5:BE999=1)*(BN5:BN999="H")*(H5:H999="Y"))

or

=SUMPRODUCT(--(BE5:BE999=1),--(BN5:BN999="H"),--(H5:H999="Y"))

the latter is supposed to be slightly faster
 
I copied your formula and am getting a result of #value.
Is it because SUMPRODUCT is expecting all numbers? I'm
wondering if I can even calculate the summary statistic
that I'm looking for. The idea is to total all the cells
in the BE column that equal 1 that correspond to entries
in BN and H that meet certain values. Maybe I need to go
in steps and add columns to my spreadsheet, and only then
calculate totals?
 
The values in BE5:BE999 must be numbers, not text. Even if they all look
like numbers, they may have been entered as text. Make sure these cells are
all formatted as number (or general), NOT text. Then copy a blank cell
(again, one NOT formatted as text), select BE5:BE999 and use Edit > Paste
Special > Add. This should force any values that are text to become numbers.
 
Peo Sjoblom said:
Kristina,

=SUMPRODUCT((BE5:BE999=1)*(BN5:BN999="H")*(H5:H999="Y"))

or

=SUMPRODUCT(--(BE5:BE999=1),--(BN5:BN999="H"),--(H5:H999="Y"))

the latter is supposed to be slightly faster
Click to expand...

No, it's not. They perform equally. The difference is that the latter obeys
the syntax of SumProduct, that is, it's kosher. So does

=SUMPRODUCT((BE5:BE999=1)+0,(BN5:BN999="H")+0,(H5:H999="Y")+0)

The first formula is an "effectively" single-array SumProduct formula, the
latter three.

If SumProduct would coerce the result arrays ensuing from condtionals
implicitly (without the need for --, +0, the slower *1, etc) as does DATE
with text-formatted numeric arguments...

=DATE(LEFT(D6,4),RIGHT(D6,2),1)

SumProduct formulas will operate faster.
 
Back
Top