count formula

[Fawn Lagimodiere]

Hi I have a huge spreadsheet

Column A has numbers
Column B has dates

If I wanted to write a formula that shows count the number of times there is
a date in Column "B" based on how many times the number "9" shows up on
column A. Is this possible with a nested CountA formula?

Any help would be appreciated.

Thanks

 

[Per Jessen]

Hi

I suppose Column B either has a valid date or is empty. Then this SumProduct
formula is what you need:

=SUMPRODUCT(--(A2:A1000=9);--(B2:B1000<>""))

Adjust the range as required, just notice the number of rows in each
statement has to be equal.

Regards,
Per

 

[Fawn Lagimodiere]

Hi I tried this formula and not too sure how the formula is written
=SUMPRODUCT(--(A2:A1000=9);--(B2:B1000<>"")) are the dashes suppose to be
there? Also column B needs to match another criteria but the criteria needs
to match a date.

Sorry for so many questions

 

[Per Jessen]

Hi

The dashes are used to turn True/False for each statement into values (1/0)
which are used to calculate the result.

If column B needs to match to match a date in C2 then try this formula:

=SUMPRODUCT(--(A2:A1000=9);--(B2:B1000=C2))

Regards,
Per