formula to count occurrences

[Libby]

Hi there,

Column A has in it a list of dates. It's quite a long list but some rows are
necessarily blank.

For the same number of rows, each cell in column B has a number in it.

Is there a formula that will display in one cell, say C1, the total of
number of 1's that occur in column B where there is a blank in Column A.


Many thanks in advance.

 

[PCLIVE]

If you mean that the cell in column A is empty, then:
=SUMPRODUCT(--(B1:B100=1),--(A1:A100=""))

If by "there is a blank" you mean there is a space in the cell, then you'd
have to modify the formula.

HTH,
Paul

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A100=""),--(B1:B100=1))

 

[Pete_UK]

Try this:

=SUMPRODUCT((A1:A1000="")*(B1:B1000=1))

Hope this helps.

Pete

 

[JP]

This will do what you want. Adjust ranges accordingly.

=SUMPRODUCT((A1:A10="")*(B1:B10=1))


HTH,
JP

 

[Libby]

Thanks.

These all work marvellously