I have a sheet with 2 columns, A & B. Column A contains list of market (non
unique) and column B contains dates. I am looking for a way to perform a
count using both coulmns. Say for instance, I wan t to know how many MarketA,
or MarketB in column A has a date in column B, how would I go about writing
that formular?
I tried this =COUNTA(IF((D18:D1769<"")*(B18:B1769=B5),)) but it only
results in 1.

Try:

=sumproduct(--((D18:D1769<""),--(B18:B1769=B5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ayo wrote:

I have a sheet with 2 columns, A & B. Column A contains list of market (non
unique) and column B contains dates. I am looking for a way to perform a
count using both coulmns. Say for instance, I wan t to know how many MarketA,
or MarketB in column A has a date in column B, how would I go about writing
that formular?
I tried this =COUNTA(IF((D18:D1769<"")*(B18:B1769=B5),)) but it only
results in 1.

--

Dave Peterson

I found the answer in another posting. Thanks


"Ayo" wrote:

I have a sheet with 2 columns, A & B. Column A contains list of market (non
unique) and column B contains dates. I am looking for a way to perform a
count using both coulmns. Say for instance, I wan t to know how many MarketA,
or MarketB in column A has a date in column B, how would I go about writing
that formular?
I tried this =COUNTA(IF((D18:D1769<"")*(B18:B1769=B5),)) but it only
results in 1.

Thanks Dave. One other question. Can it be used with more than 2 criteria i.e.,
SUMPRODUCT(--(Baseline!$A$5:$A$1756=$B$2),--(Baseline!$D$5:$D$1756=B$3),--(Baseline!$E$5:$E$1756="Projected"))

Thanks

"Dave Peterson" wrote:

Try:

=sumproduct(--((D18:D1769<""),--(B18:B1769=B5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ayo wrote:

I have a sheet with 2 columns, A & B. Column A contains list of market (non
unique) and column B contains dates. I am looking for a way to perform a
count using both coulmns. Say for instance, I wan t to know how many MarketA,
or MarketB in column A has a date in column B, how would I go about writing
that formular?
I tried this =COUNTA(IF((D18:D1769<"")*(B18:B1769=B5),)) but it only
results in 1.

--

Dave Peterson

What happened when you tried it?

Ayo wrote:

Thanks Dave. One other question. Can it be used with more than 2 criteria i.e.,
SUMPRODUCT(--(Baseline!$A$5:$A$1756=$B$2),--(Baseline!$D$5:$D$1756=B$3),--(Baseline!$E$5:$E$1756="Projected"))

Thanks

"Dave Peterson" wrote:

Try:

=sumproduct(--((D18:D1769<""),--(B18:B1769=B5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ayo wrote:

I have a sheet with 2 columns, A & B. Column A contains list of market (non
unique) and column B contains dates. I am looking for a way to perform a
count using both coulmns. Say for instance, I wan t to know how many MarketA,
or MarketB in column A has a date in column B, how would I go about writing
that formular?
I tried this =COUNTA(IF((D18:D1769<"")*(B18:B1769=B5),)) but it only
results in 1.

--

Dave Peterson


--

Dave Peterson