formula to count occurrences
 

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.

formula to count occurrences
 

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

--

"Libby" wrote in message
...
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.

formula to count occurrences
 

Try this:

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


--
Biff
Microsoft Excel MVP


"Libby" wrote in message
...
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.

formula to count occurrences
 

Try this:

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

Hope this helps.

Pete

"Libby" wrote in message
...
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.

formula to count occurrences
 

This will do what you want. Adjust ranges accordingly.

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


HTH,
JP


On Mar 27, 1:46*pm, Libby wrote:
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.

thanks
 

Thanks.

These all work marvellously