I am trying to count the number of occurences which occur in 2 columns using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

modify to suit
=SUMPRODUCT((A2:A22={0,1})*(B2:B22="brkd"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

You can't use the whole column in SUMPRODUCT.
--
David Biddulph

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

I have changed the formula to
=SUMPRODUCT(('CM(Sched)'!AP:AP="BRKD")*('CM(Sched) '!S:S={0,1}))
I still get the #NUM! error.

Thanks,
Joe M.

"Don Guillett" wrote:

modify to suit
=SUMPRODUCT((A2:A22={0,1})*(B2:B22="brkd"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

As David said,
ap2:ap22

Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
I have changed the formula to
=SUMPRODUCT(('CM(Sched)'!AP:AP="BRKD")*('CM(Sched) '!S:S={0,1}))
I still get the #NUM! error.

Thanks,
Joe M.

"Don Guillett" wrote:

modify to suit
=SUMPRODUCT((A2:A22={0,1})*(B2:B22="brkd"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same
worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

I'm making progress. The formula is now
=SUMPRODUCT(('CM(Sched)'!AP2:AP5000="BRKD")*('CM(S ched)'!S2:S5000={0,1}))
Now the error is #N/A. Also, Column S contains the formula =IF(F800,1,0).

Thanks,
Joe M.


"David Biddulph" wrote:

You can't use the whole column in SUMPRODUCT.
--
David Biddulph

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.

Try trimming your text

=SUMPRODUCT((TRIM('CM(Sched)'!AP2:AP5000)="BRKD")* ('CM(Sched)'!S2:S5000={0,1}))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
I'm making progress. The formula is now
=SUMPRODUCT(('CM(Sched)'!AP2:AP5000="BRKD")*('CM(S ched)'!S2:S5000={0,1}))
Now the error is #N/A. Also, Column S contains the formula =IF(F800,1,0).

Thanks,
Joe M.


"David Biddulph" wrote:

You can't use the whole column in SUMPRODUCT.
--
David Biddulph

"Joe M." wrote in message
...
I am trying to count the number of occurences which occur in 2 columns
using:
=SUMPRODUCT(--('CM(Sched)'!AP:AP="BRKD"),--('CM(Sched)'!S:S=0))
In worksheet CM(Sched) column AP is the text "BRKD". In the same
worksheet
column S has either a 0 or 1 (numeric).
S AP
0 DELTA
0 BRKD
1 BRKD
0 BRKD
The result should be 2. I get the error #NUM!. Can someone help?
Thanks,
Joe M.